Inner Join
SQL Query :
SELECT * FROM A INNER JOIN B ON A.KEY = B.KEY
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
Intersectionwhich is denoted by A ∩ B, the set containing all elements of A that also belong to B.
Left Join
SQL Query :
SELECT * FROM A LEFT JOIN B ON A.KEY = B.KEY
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
SQL Query :
SELECT * FROM A RIGHT JOIN B ON A.KEY = B.KEY
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
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
Full Outer Join
SQL Query :
SELECT * FROM A FULL OUTER JOIN B ON A.KEY = B.KEY
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.
Left Outer Join
SQL Query :
SELECT * FROM A LEFT OUTER JOIN B ON A.KEY = B.KEY
Definition :
This SQL query retrieves all columns from table A and combines them with matching rows from table B based on the common values in the "KEY" column. It includes all rows from table A, along with the corresponding matching rows from table B. If there are no matches in table B, the columns from table B will contain null values.
Right Outer Join
SQL Query :
SELECT * FROM A RIGHT OUTER JOIN B ON A.KEY = B.KEY
Definition :
This SQL query retrieves all columns from table B and combines them with matching rows from table A based on the common values in the "KEY" column. It includes all rows from table B, along with the corresponding matching rows from table A. If there are no matches in table A, the columns from table A will contain null values.
Full Outer Join without Intersection
SELECT * FROM A FULL OUTER JOIN B ON A.KEY = B.KEY
Definition :
This SQL query performs a full outer join between table A and table B based on the common values in the "KEY" column. It retrieves all columns from both tables and combines them. The result includes all rows from both tables, matching rows where the "KEY" values are the same. If there are no matches in either table for a specific "KEY" value, the columns from the respective table will contain null values in the result.
My professional page : https://danyson.netlify.app/
Top comments (1)
Thanks for this very useful post. I just wanted to add that, there is a very easy way now, to test all the SQLs described here, using the free & portable tools, mentioned in my latest post here : dev.to/linuxguist/learn-sql-quickl...