Prev Next

SQL Joins Intro

SQL Joins are used to combine rows from two or more tables based on a related column. Joins allow you to query data that is spread across multiple tables in a relational database.

Types of Joins

  • INNER JOIN: Returns rows with matching values in both tables.
  • LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
  • RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
  • FULL JOIN: Returns rows when there is a match in either table.
  • SELF JOIN: Joins a table to itself.
  • UNION: Combines the result sets of two SELECT queries, removing duplicates.

Table Creation Examples

Let's create four sample tables to demonstrate different types of joins:

1. Creating the FreedomFighters Table

CREATE TABLE FreedomFighters (
    FighterID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    City VARCHAR(50),
    BirthDate DATE
);

2. Creating the Contributions Table

CREATE TABLE Contributions (
    ContributionID INT PRIMARY KEY,
    FighterID INT,
    Amount DECIMAL(10, 2),
    ContributionDate DATE,
    FOREIGN KEY (FighterID) REFERENCES FreedomFighters(FighterID)
);

3. Creating the Cities Table

CREATE TABLE Cities (
    CityID INT PRIMARY KEY,
    CityName VARCHAR(50) UNIQUE,
    Population INT
);

4. Creating the Events Table

CREATE TABLE Events (
    EventID INT PRIMARY KEY,
    FighterID INT,
    EventName VARCHAR(100),
    EventDate DATE,
    FOREIGN KEY (FighterID) REFERENCES FreedomFighters(FighterID)
);

Inserting Sample Data

Here is some sample data for the tables:

Inserting Data into FreedomFighters

INSERT INTO FreedomFighters (FighterID, Name, City, BirthDate) VALUES
(1, 'Mahatma Gandhi', 'Porbandar', '1869-10-02'),
(2, 'Subhas Chandra Bose', 'Cuttack', '1897-01-23'),
(3, 'Bhagat Singh', 'Lyalpur', '1907-09-28'),
(4, 'Rani Lakshmi Bai', 'Varanasi', '1828-11-19');

Inserting Data into Contributions

INSERT INTO Contributions (ContributionID, FighterID, Amount, ContributionDate) VALUES
(1, 1, 1000.00, '1920-08-15'),
(2, 2, 1500.00, '1943-07-04'),
(3, 3, 500.00, '1928-03-23'),
(4, 4, 800.00, '1857-06-18');

Inserting Data into Cities

INSERT INTO Cities (CityID, CityName, Population) VALUES
(1, 'Porbandar', 200000),
(2, 'Cuttack', 600000),
(3, 'Lyalpur', 300000),
(4, 'Varanasi', 1200000);

Inserting Data into Events

INSERT INTO Events (EventID, FighterID, EventName, EventDate) VALUES
(1, 1, 'Dandi March', '1930-03-12'),
(2, 2, 'INA Formation', '1942-10-21'),
(3, 3, 'Lahore Protest', '1929-12-19'),
(4, 4, 'Battle of Jhansi', '1858-03-23');

Retrieving Data with Joins

Example: Using INNER JOIN

SELECT FreedomFighters.Name, Contributions.Amount
FROM FreedomFighters
INNER JOIN Contributions ON FreedomFighters.FighterID = Contributions.FighterID;

Output:

NameAmount
Mahatma Gandhi1000.00
Subhas Chandra Bose1500.00
Bhagat Singh500.00
Rani Lakshmi Bai800.00

Example: Using LEFT JOIN

SELECT FreedomFighters.Name, Events.EventName
FROM FreedomFighters
LEFT JOIN Events ON FreedomFighters.FighterID = Events.FighterID;

Output:

NameEventName
Mahatma GandhiDandi March
Subhas Chandra BoseINA Formation
Bhagat SinghLahore Protest
Rani Lakshmi BaiBattle of Jhansi
Prev Next