What are joins in SQL ?
SQL joins are used to combine two or more tables using column(s) as reference.
Nowadays most databases are normalized so it’s important to know the basics of SQL joins.
SELECT *
FROM demo_tab_1 as t1
JOIN demo_tab_2 as t2
ON t1.COL_x = t2.COL_y ;In the above example, we have two tables named demo_tab_1 and demo_tab_2. We used the join clause to join them, ON clause is used to specify the column names by which the tables will be joined.
Types of JOINS in SQL
- LEFT JOIN
- RIGHT JOIN
- INNER JOIN
- FULL OUTER JOIN
LEFT JOIN
Left join is used when we want all the rows from the left table and matching rows from the right table.
NOTE: The first table is considered as the table on the left side.
EXAMPLE:
| Customer_id | product |
| 1 | Samsung Phone 32gb |
| 3 | Cuppa noodles 50g |
| 7 | Digital clock |
| Customer_id | Customer_name |
| 1 | James Cameroon |
| 2 | Shyam Sharma |
| 3 | Manjeet Patel |
| 7 | Kadar Khan |
SELECT *
FROM ORDER_INFO AS ORD
LEFT JOIN CUSTOMER_INFO AS CUST
ON ORD.CUSTOMER_ID = CUST.CUSTOMER_ID ;| Customer_id | product | Customer_id | Customer_name |
| 1 | Samsung Phone 32gb | 1 | James Cameroon |
| 3 | Cuppa noodles 50g | 3 | Manjeet Patel |
| 7 | Digital clock | 7 | Kadar Khan |
As you can see with the help of LEFT JOIN we got all the above records. We used customer_id as
RIGHT JOIN
Right join is used when we want all the rows from the right table and the matching rows from the left table.
EXAMPLE
| Customer_id | product |
| 1 | Samsung Phone 32gb |
| 3 | Cuppa noodles 50g |
| 7 | Digital clock |
| Customer_id | Customer_name |
| 1 | James Cameroon |
| 2 | Shyam Sharma |
| 3 | Manjeet Patel |
| 7 | Kadar Khan |
SELECT *
FROM order_info ORD
RIGHT JOIN customer_info CUST
ON ORD.CUSTOMER_ID = CUST.CUSTOMER_ID ;| Customer_id | Customer_name | customer_id | product |
| 1 | James Cameroon | 1 | Samsung Phone 32gb |
| 2 | Shyam Sharma | NULL | NULL |
| 3 | Manjeet Patel | 3 | Cuppa noodles 50g |
| 7 | Kadar Khan | 7 | Digital clock |
As you can see the table above shows all the rows from the right table and matching rows from the left table. NULL is present on the place where there is no matching values
INNER JOIN
Inner Join is used when we want only the matching rows from both of the tables.
EXAMPLE:
| Customer_id | product |
| 1 | Samsung Phone 32gb |
| 3 | Cuppa noodles 50g |
| 7 | Digital clock |
| Customer_id | Customer_name |
| 1 | James Cameroon |
| 2 | Shyam Sharma |
| 3 | Manjeet Patel |
| 7 | Kadar Khan |
SELECT *
FROM ORDER_INFO ORD
INNER JOIN CUSTOMER_INFO CUST
ON ORD.CUSTOMER_ID = CUST.CUSTOMER_ID ;| Customer_id | product | Customer_id | Customer_name |
| 1 | Samsung Phone 32gb | 1 | James Cameroon |
| 3 | Cuppa noodles 50g | 3 | Manjeet Patel |
| 7 | Digital clock | 7 | Kadar Khan |
FULL OUTER JOIN
It is used when we want all the rows from both of the tables regardless if the rows match or not.
EXAMPLE:
| Customer_id | product |
| 1 | Samsung Phone 32gb |
| 3 | Cuppa noodles 50g |
| 7 | Digital clock |
| 26 | Logitech keyboard and mouse combo |
| Customer_id | Customer_name |
| 1 | James Cameroon |
| 2 | Shyam Sharma |
| 3 | Manjeet Patel |
| 7 | Kadar Khan |
SELECT
FROM ORDER_INFO ORD
FULL OUTER JOIN CUSTOMER_INFO CUST
ON ORD.CUSTOMER_ID = CUST.CUSTOMER_ID ;| Customer_id | Customer_name | product |
| 1 | James Cameroon | Samsung Phone 32gb |
| 3 | Manjeet Patel | Cuppa noodles 50g |
| 7 | Kadar Khan | Digital clock |
| 26 | NULL | Logitech keyboard and mouse combo |
| 2 | Shyam Sharma | NULL |
Above example shows the use of FULL OUTER JOIN i.e. matching rows from both and tables + unmatched rows from the left table + unmatched rows from the right table.
THANKS FOR READING!
SOURCES : Microsoft T-SQL Documentation on JOINS
