Description

The SQL keyword JOIN is used to join two tables, and the type of join determines the rows in the result set.

Here is a list of types of joins.

Type of Join Description
INNER JOIN It returns all the rows that have a match between the joined tables.
LEFT JOIN It returns all rows from the left table along with the data from the right table for the matching records.
In case, the matching rows are not found on the right table, a NULL value is used for the columns coming from that table.
RIGHT JOIN It returns all rows from the right table along with the data from the left table for the matching records.
In case, the matching rows are not found on the left table, a NULL value is used for the columns coming from that table.
FULL JOIN It returns all the rows from both the tables along with the data from the other table for the matching records.
It combines the functionality of the LEFT JOIN and RIGHT JOIN, so as the result set.
CROSS JOIN It returns a Cartesian product or multiplication of both the joined tables, where each row of one table is combined with each row of another table.
So, it returns each possible combination of the data from the two tables.
Self Join It is a regular join without a JOIN clause, where the table is joined with itself.

INNER JOIN

The below SQL fetches data from specific columns of the joined tables.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

The below SQL fetches data from all columns of the joined tables.

Run this on IDE

SELECT *
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

The below SQL fetches data from more than two tables joined.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name, shipper_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN shippers ON orders.shipper_id = shippers.shipper_id;

LEFT JOIN

The below SQL fetches data from specific columns of the joined tables.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

The below SQL fetches data from all columns of the joined tables.

Run this on IDE

SELECT *
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

RIGHT JOIN

The below SQL fetches data from specific columns of the joined tables.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

The below SQL fetches data from all columns of the joined tables.

Run this on IDE

SELECT *
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

FULL JOIN

The below SQL fetches data from specific columns of the joined tables.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
FULL JOIN customers ON orders.customer_id = customers.customer_id;

The below SQL fetches data from all columns of the joined tables.

Run this on IDE

SELECT *
FROM orders
FULL JOIN customers ON orders.customer_id = customers.customer_id;

CROSS JOIN

The below SQL fetches data from specific columns of the joined tables.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
CROSS JOIN customers;

The below SQL fetches data from all columns of the joined tables.

Run this on IDE

SELECT *
FROM orders
CROSS JOIN customers;

SELF JOIN

The below SQL fetches data from a table using a self-join.

Run this on IDE

SELECT a.customer_name as customer_name1, b.customer_name as customer_name2, a.city
FROM customers a, customers b
WHERE a.customer_id <> b.customer_id
AND a.city = b.city
ORDER BY a.city;

Related Links