DEV Community

loading...

SQL Joins

danyson profile image Danyson ・3 min read

Inner Join

image

SQL Query :

SELECT * FROM A INNER JOIN B ON A.KEY = B.KEY
Enter fullscreen mode Exit fullscreen mode

Definition :

Select tables A & B and compare key values of table A and table B and return records which only have the identical key values in both tables of A & B. This is similar to the Set theory operation Intersection which is denoted by A ∩ B, the set containing all elements of A that also belong to B.

Left Join

image

SQL Query :

SELECT * FROM A LEFT JOIN B ON A.KEY = B.KEY
Enter fullscreen mode Exit fullscreen mode

Definition :

Select tables A & B and compare key values of table A and table B and return all records of A which is in the left side of the sql query but in case of B which is in right side of sql query, only returns records that have the same key values of table A.

Right Join

image

SQL Query :

SELECT * FROM A RIGHT JOIN B ON A.KEY = B.KEY
Enter fullscreen mode Exit fullscreen mode

Definition :

Select tables A & B and compare key values of table A and table B and return all records of B which is in the right side of the sql query but in case of A which is in left side of sql query, only returns records that have the same key values of table B.

Note :
1. ON clause is the more general than USING clause. One can join tables on a column, a set of columns and even a condition. For example:

SELECT * FROM city JOIN country ON city.country_code = country.country_code
Enter fullscreen mode Exit fullscreen mode

2. USING is useful when both tables share a column of the exact same name on which they join. For example:

SELECT city.name, city_id FROM city JOIN country USING city_id
Enter fullscreen mode Exit fullscreen mode

Full Outer Join

image

SQL Query :

SELECT * FROM A FULL OUTER JOIN B ON A.KEY = B.KEY
Enter fullscreen mode Exit fullscreen mode

Definition :

Select tables A & B and compare key values of table A and table B and return all records of both A & B and fill in NULL for missing matches on either side. This is similar to the set theory operation Union of the sets A and B, where A ∪ B, is the set of distinct elements that belong to set A or set B, or both.

Left Outer Join

image
SQL Query :

SELECT * FROM A LEFT OUTER JOIN B ON A.KEY = B.KEY WHERE B.KEY IS NULL
Enter fullscreen mode Exit fullscreen mode

Definition :

Left outer join returns set of records from A, with the matching records in B (which are available). If there is no match, the right side will have null. This is similar to the set theory operation Set Difference ‘A – B’, which is the set containing elements that are in A but not in B. For example : all elements of A except the elements of B.

Right Outer Join

image
SQL Query :

SELECT * FROM A RIGHT OUTER JOIN B ON A.KEY = B.KEY WHERE A.KEY IS NULL
Enter fullscreen mode Exit fullscreen mode

Definition :

Right outer join returns set of records from B, with the matching records in A (which are available). If there is no match, the left side will have null. This is similar to the set theory operation Set Difference ‘B – A’, which is the set containing elements that are in B but not in A. For example : all elements of B except the elements of A.

Full Outer Join without Intersection

image

SELECT * FROM A FULL OUTER JOIN B ON A.KEY = B.KEY WHERE A.KEY IS NULL OR B.KEY IS NULL
Enter fullscreen mode Exit fullscreen mode

Definition :

Full outer join without intersection returns the set of records only in table A, but not in table B or the set of records only in table B, but not in table A, we perform the same left outer join or right outer join, then exclude the records we don't want from the right side or left side via a where clause. Which is similar to the set operation A – B ∪ B – A.

My Personal Blog : https://danyson.github.io/

Discussion (0)

Forem Open with the Forem app