SQL - JOINS

---

---

SQL JOIN

The JOIN clause in SQL is used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables as if they were a single table.

Using JOIN helps in organizing data stored in different tables and returning a meaningful dataset.

Syntax

Following is the basic syntax of the SQL JOIN clause:

SELECT columns
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Here,

• table1 and table2 are the tables to be joined.
• column_name is the common column used to match rows between the tables.

Types of SQL JOINS

There are different types of SQL joins used to combine data from multiple tables. They are as follows:

• INNER JOIN returns only the records that have matching values in both tables.
• LEFT JOIN returns all records from the left table and matching ones from the right table.
• RIGHT JOIN returns all records from the right table and matching ones from the left table.
• FULL JOIN returns all records when there is a match in either table.
• SELF JOIN is a join where a table is joined with itself to compare rows within the same table.
• CROSS JOIN returns the Cartesian product of the two tables, pairing each row from one table with all rows from the other.

Let us discuss all of them one-by-one further in this tutorial

SQL INNER JOIN

The INNER JOIN keyword in SQL selects only the rows that have matching values in both tables. If there is no match, the row is not included in the result.

Syntax

Following is the syntax of the SQL INNER JOIN clause:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example

Assume we have created a table with name CUSTOMERS in MySQL database using CREATE TABLE statement as shown below:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Following query inserts values into this table using the INSERT statement:

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The CUSTOMERS table is as follows:

Now, creating the second table ORDERS using CREATE TABLE statement as shown below:

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE DATETIME NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT INT NOT NULL,      
   PRIMARY KEY (OID)
);

Following query inserts values into this table using the INSERT statement:

INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000),
(100, '2009-10-08 00:00:00', 3, 1500),
(101, '2009-11-20 00:00:00', 2, 1560),
(103, '2008-05-20 00:00:00', 4, 2060);

The ORDERS table is as follows:

Now, we will retrieve customer names along with their order amounts by joining the CUSTOMERS and ORDERS tables using INNER JOIN:

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.AMOUNT
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Following is the output obtained:

SQL LEFT JOIN

The LEFT JOIN keyword in SQL returns all rows from the left table, and the matched rows from the right table. If no match exists, NULL values are returned for columns from the right table.

Syntax

Following is the syntax of the SQL LEFT JOIN clause:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example

Here, we will retrieve all customers along with their orders, even if they have not placed any order:

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.AMOUNT
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

We get the output as shown below:

SQL RIGHT JOIN

The RIGHT JOIN keyword in SQL returns all rows from the right table, and the matched rows from the left table. If there is no match, NULL values are returned from the left table.

Syntax

Following is the syntax of the SQL RIGHT JOIN clause:

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example

In this example, we will retrieve all orders along with the customer details, even if no matching customer exists:

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.AMOUNT
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

The result is displayed as follows:

SQL FULL JOIN

The SQL FULL JOIN keyword returns all rows from both tables, with NULLs where there is no match. Some databases use FULL OUTER JOIN.

Syntax

Following is the syntax of the SQL FULL JOIN clause:

SELECT column1, column2, ...
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

MySQL does not support FULL JOIN directly. You can imitate it using a UNION of LEFT and RIGHT JOIN.

Example

Here, we will retrieve all customers and orders, showing NULL where there is no matching record in the SQL Server database:

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.AMOUNT
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

The table produced is as follows:

SQL SELF JOIN

A SELF JOIN in SQL is a regular join where a table is joined to itself. This is useful for comparing rows within the same table.

Syntax

Following is the syntax of the SQL SELF JOIN clause:

SELECT A.column1, B.column1, ...
FROM table_name A, table_name B
WHERE condition;

Example

This query uses a SELF JOIN on the CUSTOMERS table to match customers with others of the same age. It returns each pair's names along with their shared age, excluding matches to themselves:

SELECT A.NAME AS CUSTOMER1, B.NAME AS CUSTOMER2, A.AGE
FROM CUSTOMERS A, CUSTOMERS B
WHERE A.AGE = B.AGE AND A.ID <> B.ID;

The table produced is as follows:

SQL CROSS JOIN

A CROSS JOIN in SQL returns the Cartesian product of two tables. A Cartesian product means every row from the first table is combined with every row from the second table, producing all possible row combinations.

For example, if Table A has 3 rows and Table B has 4 rows, the result of a CROSS JOIN will have 3 × 4 = 12 rows as shown below:

-- Table A
A1
A2
A3

-- Table B
B1
B2
B3
B4

-- CROSS JOIN result
A1 B1
A1 B2
A1 B3
A1 B4
A2 B1
A2 B2
...

Syntax

Following is the syntax for CROSS JOIN in SQL:

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Example

The following query lists every possible combination of a customer's name and an order ID from the two tables using CROSS JOIN:

SELECT CUSTOMERS.NAME, ORDERS.OID
FROM CUSTOMERS
CROSS JOIN ORDERS;

The table produced is as follows:

If CUSTOMERS has n rows and ORDERS has m rows, the result will have n × m rows.

SQL JOIN with Multiple Tables

You can join more than two tables by chaining multiple JOIN clauses together. Each JOIN matches rows based on a related column between the tables.

Syntax

Following is the syntax of joining multiple tables in SQL:

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;

Example

In the following example, we retrieve customer names, their order amounts, and the corresponding order dates by joining the CUSTOMERS and ORDERS tables along with an additional table PAYMENTS.

To do this, let us first create the PAYMENTS table:

CREATE TABLE PAYMENTS (
   PID INT NOT NULL,
   OID INT NOT NULL,
   PAYMENT_DATE DATETIME NOT NULL,
   AMOUNT DECIMAL(10,2),
   PRIMARY KEY (PID)
);

Now, we insert some values to the PAYMENTS table:

INSERT INTO PAYMENTS VALUES
(1, 102, '2009-10-10 00:00:00', 3000.00),
(2, 101, '2009-11-22 00:00:00', 1560.00),
(3, 103, '2008-05-22 00:00:00', 2060.00);

Now, join all three tables in SQL using the following query:

SELECT CUSTOMERS.NAME, ORDERS.AMOUNT, PAYMENTS.PAYMENT_DATE
FROM CUSTOMERS
INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
INNER JOIN PAYMENTS ON ORDERS.OID = PAYMENTS.OID;

The result is displayed as follows: