SQL Joins
Tags: sql, database, tutorial
Published: 2021-05-08 22:28:45

#Inner Join
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fdtwiofcfor43bpsub7l.png)

**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
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wh6lg2ps6kxlsbwu2r1l.png)

**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
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/27s6kaq3y84zilo959cx.png)
 
**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
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vq4b9zot1bu8fgawecqg.png)

**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
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6bz6x0gxm6cts3bsa2kv.png) 
**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
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vyhnh7pgapjgcin2ghl6.png)
**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
 ![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mvybx6nn3zo5ax2gbpsj.png)
```
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/](https://danyson.netlify.app/)