Day 5: Advance SQL For Data Science | by Sunita Rawat | Jan, 2023

This blog contains type of joins like Inner join, Left join, Right join , Full join, Self join and Cross join.

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

A self-join is a regular join, in which the table joins itself.

Syntax of self-join:

SELECT column_name(s) from table1 T1, table1 T2
WHERE condition;

Query: select T1.user_id , T1.name, T2.user_id, T2.name from user T1, user T2;

NOTE: T1 and T2 are different table aliases for the same table.

The INNER JOIN keyword selects records that have matching values in both tables.

Syntax of Inner join :

SELECT column_name(s) FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Query: select * from user u
inner join Guest G on G.Guest_user_id = u.user_id;

Inner join provides only rows in which data is matched on both sides of the table.

The LEFT JOIN keyword returns all records from the left table, and the matched records from the right table.

Syntax of Left-join:

SELECT column_name(s) FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Query: select * from user u left join Guest G on G.Guest_user_id = u.user_id;

In Left join, we are applying left join to the user table which is mentioned first (In Query). It will return all the rows from table 1 i.e. user table and only matched rows from table 2 i.e. Guest table.

The RIGHT JOIN keyword returns all records from the right table and the matched records from the left table.

Syntax of Right-join:

SELECT column_name(s) FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Query: select * from user u
right join Guest G on G.Guest_user_id = u.user_id;

In right join, it returns all the rows from the right table i.e. Guest table and only matched rows from the first table i.e. user table.

The Full JOIN keyword returns all records when there is a match in either the left or the right table.

Syntax of full join:

SELECT column_name(s) FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

The CROSS JOIN keyword returns all records from both tables (table1 and table2).

Syntax of Cross-join:

SELECT column_name(s) FROM table1
CROSS JOIN table2;

Query: select * from user u cross join Guest G

Note: On clause is optional in Self and cross join.

We can use the Aggregate, ranking and etc functions with joins and can manipulate queries as we want.

Here is a short example of left join with group by and having clause.

Query: select user_id, u.name, min(u.age) from user u
left join Guest G on G.Guest_user_id = u.user_id
Group by user_id, u.name having min(G.age) <= 30;