JOINS IN SQL
1. SQL JOIN
The SQL Join help in retrieving data from two or more database tables. The tables are mutually related using primary keys and foreign keys.
Type of Join
INNER JOIN
The INNER JOIN is used to print rows from both tables that satisfy the given condition. For example, the user wants to get a list of users who have rented movies together with titles of movies rented by them. Users can use an INNER JOIN for that, which returns rows from both tables that satisfy with given conditions.
Fig. INNER JOIN
The INNER JOIN keyword selects records that have matching values in both the tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
LEFT JOIN
The LEFT JOIN returns all the records from the table1 (left table) and the matched records from the table2 (right table). The output is NULL from the right side if there is no match.
Left Join
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
RIGHT JOIN
The RIGHT JOIN is the opposite of LEFT JOIN. The RIGHT JOIN prints all the columns from the table2(right table) even if there no matching rows have been found in the table1 (left table). If there no matches have been found in the table (left table), NULL is returned.
RIGHT JOIN
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Full OUTER JOIN
The FULL OUTER JOIN keyword returns all records when there are a match in left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
Full Join
Tip: FULL OUTER JOIN and FULL JOIN are the same.
FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name WHERE condition;
Note: MySQL does not support the Full Join, so we can perform left join and right join separately then take the union of them.
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
SELF-JOIN
A self-JOIN is a regular join, but the table is joined with itself.
Self -JOIN Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
That’s all for the introduction to Joins in Sql! Keep your eye out for more blogs coming soon that will go into more depth on specific topics.
If you enjoy my work and want to keep up to date with the latest publications or would like to get in touch, I can be found on Medium at Suraj Gusain — Thanks!
Happy learning:)