It is the age of Data and now we Data assume data is more precious than Gold. In Data world SQL is one of the best tool used for Data Analysis.

Types of Joins

Basically, there are four types of JOINS in SQL.

• • • CROSS JOIN
    • INNER JOIN

                   –   EQUI JOIN
                               –  NATURAL JOIN

  • OUTER JOIN

                   –   LEFT JOIN (LEFT OUTER JOIN)
                   –   RIGHT JOIN (RIGHT OUTER JOIN)
                   –   FULL JOIN    (FULL OUTER JOIN)

  • SELF JOIN

For practical implementation of JOINS, we are taking two tables 

# Table Details


Products_dataset:-(product_id,product_category_name,product_name_lenght,product_description_lenght,product_photos_qty,product_weight_g,product_length_cm,product_height_cm,product_width_cm)

product_category_name_translation :- (product_category_name	product_category_name_english)

CROSS JOIN

CROSS JOIN returns cartesian product of two tables.

Cartesian Product

If table A contains m rows and table B contains n rows, then cartesian product of table A and table B will contain m*n rows.
Syntax :

# cross join syntax


SELECT * FROM tableA CROSS JOIN tableB

     or
     
SELECT * FROM tableA,tableB

Example :

SELECT * FROM products_dataset CROSS JOIN product_category_name_translation

OutPut:

INNER JOIN

return all rows from tableA where there are matching values in tableB and all columns from tableA and tableB. If there are multiple matches between tableA and tableB then all matches will return from tableA and tableB on the basis of a specified condition.

And condition has been mentioned in the syntax after the last table.
Syntax:

# Inner join syntax

SELECT * FROM tableA INNER JOIN tableB ON (tableA.x = tableB.y)


or 

SELECT * FROM tableA JOIN tableB ON (tableA.x = tableB.y)

Note: By default, JOIN is considered as INNER JOIN or EQUI JOIN.

Example :

# inner join example

SELECT * FROM products_dataset pds INNER JOIN product_category_name_translation pct
ON pds.product_category_name=pct.product_category_name

OutPut :

LEFT JOIN (LEFT OUTER JOIN) :

return all rows from tableA, and all columns from tableA and tableB. Rows in tableA with no match in tableB will have NA values in the new columns. If there are multiple matches between tableA and tableB, all combinations of the matches are returned.
Syntax :

# left join syntax

SELECT * FROM tableA LEFT JOIN tableB ON (tableA.x = tableB.y)

Example:

#  left join example

SELECT * FROM products_dataset pds LEFT JOIN product_category_name_translation pct

ON pds.product_category_name=pct.product_category_name

OutPut :

RIGHT JOIN (RIGHT OUTER JOIN)

return all rows from tableB, and all columns from tableA and tableB. Rows in tableB with no match in tableA will have NA values in the new columns. If there are multiple matches between tableA and tableB, all combinations of the matches are returned.
Syntax:

# right join syntax

SELECT * FROM tableA RIGHT JOIN tableB ON (tableA.x = tableB.y)

Example:

#  right join example

SELECT * FROM products_dataset pds RIGHT JOIN product_category_name_translation pct
ON pds.product_category_name=pct.product_category_name

OutPut: