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 Intersection which 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/