Join in SQL

SQL Join has been a very popular interview question and why not ?As need to extract data from tables have, one-to-many or many-to-many relationships between them which mostly happen in database management systems frequently.

So let’s understand these different joins in very simple way :)

Why we use joins ?

when we want to display or use such kind of data which is partially present in one table and rest present in another table.

simply we can say, JOIN clause joins two tables based upon common attributes, so whatever is common in both of the tables it will be printed or used.

Types :

JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN

JOIN/INNER JOIN

Below are two tables with some records. reg_id is the only one attribute matches or common in both of the tables. So let’s say we want to print names from Table:2 followed by st_id from Table:1

Syntax :

SELECT table_name.column_name, table_name.column_name
FROM left_table_name
INNER JOIN right_table_name ON right_table_name.column_name=left_table_name.column_name;

In our case of tables, the query will be:

SELECT table1.st_id, table2.name
FROM table1
INNER JOIN table2 ON table1.reg_id=table2.reg_id;

Desired Output :

Format of data from two different tables.

So remember that first table is the left one and second table is the right one when comparing between two tables.

INNER JOIN and simply JOIN both works the same.

Right Join

The RIGHT JOIN keyword gives all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match and a NULL is shown next to the unmatched record.

Syntax :

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

Now this time all the attributes which are common in between both tables will be printed along with the rest of all records from right table. So Zayn will be also there included in result even if it’s reg_id is not matching to any reg_id of table1.

And here goes the result of RIGHT JOIN :

null is assigned as no reg_id of Zayn matched in Table1

LEFT JOIN

They works similar to right join with one difference, The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match found and a NULL is shown next to the unmatched record.

Syntax :

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

So things go like this:

Using LEFT JOIN

FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) and right (table2) table records. Indirectly we get all the present data in both of the tables.

Whereas table1 records which are not matching with table2 records then they print null.

FULL OUTER JOIN and FULL JOIN are the same.

Using FULL JOIN

Records from both the tables will be displayed in below format :

Self Join

You can view self join as a two identical tables but in normalization, you cannot probably go for creating two copies for the same table. So you can simulate having two tables with self join.

well we don’t use any keyword like SELF JOIN

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

Above syntax indicates a,b as alias for table1 it means they both are referring to table1

SELECT a.name AS student,b.name AS Leader
FROM table1 a
JOIN table1 b ON a.st_id=b.leader_id

After executing above query, we get this :

So this was all about JOINs in SQL.

If you have more to add feel free to share your knowledge or opinion in comments.