SQL JOINS- SIMPLIFIED WITH EXAMPLES

sql-joins
Spread the love

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.

left-join-sql
EXAMPLE:
Customer_idproduct
1Samsung Phone 32gb
3Cuppa noodles 50g
7Digital clock
order_info
Customer_idCustomer_name
1James Cameroon
2Shyam Sharma
3Manjeet Patel
7Kadar Khan
customer_info
SELECT *
FROM ORDER_INFO AS ORD
LEFT JOIN CUSTOMER_INFO AS CUST
ON ORD.CUSTOMER_ID = CUST.CUSTOMER_ID ;
Customer_idproductCustomer_idCustomer_name
1Samsung Phone 32gb1James Cameroon
3Cuppa noodles 50g3Manjeet Patel
7Digital clock7Kadar 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.

right-join-sql
EXAMPLE
Customer_idproduct
1Samsung Phone 32gb
3Cuppa noodles 50g
7Digital clock
order_info
Customer_idCustomer_name
1James Cameroon
2Shyam Sharma
3Manjeet Patel
7Kadar Khan
customer_info
SELECT *
FROM order_info ORD
RIGHT JOIN customer_info CUST
ON ORD.CUSTOMER_ID = CUST.CUSTOMER_ID ;
Customer_idCustomer_namecustomer_idproduct
1James Cameroon1Samsung Phone 32gb
2Shyam SharmaNULLNULL
3Manjeet Patel3Cuppa noodles 50g
7Kadar Khan7Digital clock
customer_info

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.

inner-join-sql
EXAMPLE:
Customer_idproduct
1Samsung Phone 32gb
3Cuppa noodles 50g
7Digital clock
order_info
Customer_idCustomer_name
1James Cameroon
2Shyam Sharma
3Manjeet Patel
7Kadar Khan
customer_info
SELECT *
FROM ORDER_INFO ORD
INNER JOIN CUSTOMER_INFO CUST
ON ORD.CUSTOMER_ID = CUST.CUSTOMER_ID ;
Customer_idproductCustomer_idCustomer_name
1Samsung Phone 32gb1James Cameroon
3Cuppa noodles 50g3Manjeet Patel
7Digital clock7Kadar 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.

full-join-sql
EXAMPLE:
Customer_idproduct
1Samsung Phone 32gb
3Cuppa noodles 50g
7Digital clock
26Logitech keyboard and mouse combo
order_info
Customer_idCustomer_name
1James Cameroon
2Shyam Sharma
3Manjeet Patel
7Kadar Khan
customer_info
SELECT 
FROM ORDER_INFO ORD
FULL OUTER JOIN CUSTOMER_INFO CUST
ON ORD.CUSTOMER_ID = CUST.CUSTOMER_ID ;
Customer_idCustomer_nameproduct
1James CameroonSamsung Phone 32gb
3Manjeet PatelCuppa noodles 50g
7Kadar KhanDigital clock
26NULLLogitech keyboard and mouse combo
2Shyam SharmaNULL
RESULT

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

Read more:
SQL BASICS
SQL BASIC FUNCTIONS
SQL WINDOW FUNCTIONS