Concept of SQL joins is always confusing for beginners. We hope this post will help you understand types of SQL joins better.
1. SELECT from two tables separately
Lets start with simple SELECT query on two tables which will show all the rows in tables respectively. Check for the yellow area.
2. INNER JOIN
Inner join on two tables as name suggests will provide the data that is common between both the tables with respect to keys. The Inner join selects all rows from both participating tables as long as there is a match between the columns.
3. LEFT OUTER JOIN
Left join (or Left outer join) performs a join starting with the first (left-most) table and then any matching second (right-most) table records. Left outer join will provide you the data from table left in query along with the data that is common between two tables with respect to keys
4. RIGHT OUTER JOIN
Right outer join preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table. The RIGHT JOIN keyword returns all records from the right table (Table_B), and the matched records from the left table (Table_A). The result is NULL from the left side, when there is no match.
5. SEMI JOIN
SEMI JOIN is similar to Inner Join with less duplication from Table_B (t2). “Semi” means that we don’t really join the right hand side, we only check if a join would yield results for any given tuple.
6. ANTI SEMI JOIN
An ANTI SEMI JOIN between two tables returns rows from the first table where no matches are found in the second table. An anti-join is essentially the opposite of a semi-join. While a semi-join returns one copy of each row in the first table for which at least one match is found, an anti-join returns one copy of each row in the first table for which no match is found.
7. LEFT OUTER JOIN with exclusion
This JOIN will return results similar to ANTI SEMI JOIN. However this is the replacement for a ‘NOT IN’
8. RIGHT OUTER JOIN with exclusion
This JOIN is replacement for ‘NOT IN’
9. FULL OUTER JOIN
The FULL OUTER JOIN keyword return all records when there is a match in either left (Table_A) or right (Table_B) table records.
10. CROSS JOIN
The CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product. If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN
11. FULL OUTER JOIN with exclusion
This JOIN is replacement for a double ‘NOT IN’
12. TWO INNER JOINS
You can run INNER JOIN on multiple tables. Below example will show results common in all tables with respect to the key column.
13. TWO FULL OUTER JOINS
Similar to above type, here the result will be the rows from all tables along with the common in two or multiple tables.
14. INNER JOIN AND LEFT OUTER JOIN
This JOIN will provide you the data from Table_A (t1) which is common between two or multiple tables.
15. TWO LEFT OUTER JOINS
Result set will be the data from lest most table (Table_A) along with the data common between two or multiple tables.
We hope these images will help you understand the SQL JOINS. Do follow us on social profiles for useful tech tips and updates.