Learn SQL: Microsoft SQL Server - Episode 16: Outer Join | Left and Right
Tags: sql, learning, database, programming
Published: 2022-01-08 22:33:25


![hi-everyone](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/69dr51pdk5j5k1v9leav.gif)

In this last entry, I want to revisit how **Join** operations work, we know so far they will match the data from one table to another table and pull the joining records.

However sometimes that might not be enough. In some cases you might want to pull the records from one table and then joining records from the other table with empty values, whenever there is no matching records.

>Thats sounds confusing so lets dive into <abbr title="SQL Server Management Studio">SSMS<abbr> to make it more clear.

Lets write a SQL query that will pull all customers who have placed an order. Any number of orders. We will be using **Sales.SalesOrder.Header** and **Sales.Customer** tables.

**Sales.Customer** is the list of customers and **Sales.SalesOrderHeader** is the list of all the orders.

We will construct this query as follows. 

```
Select * from Sales.Customer a
join Sales.SalesOrderHeader b
on a.CustomerID = b.CustomerID
```
>I made use of aliases to make it a little easier to construct this query statement, by giving **Sales.Customer** an Alias of **a** and **Sales.SalesOrderHeader** the Alias of **b**

We basically want to join the two tables based on the matching **CustomerID**.

![Join-tables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mbt8o1de9u36f7byftcy.gif)

Notice how many results was returned from this statement. 31465 total rows.

We will add a comment inside our query to indicate this result. A comment is not evaluated by SQL. It is typically used by us as programmers and developers to explain our intent to other programmers and our future selfs when we return to our code.

The way we write comments in SQL is by prefixing our comment with two dashes.

```
-- total (comment)
```

for example
```
-- total number of records
```

If we want to get a list of all the customers, whether they have placed orders or not for this result set, if they haven't placed an order then all the sales records will be **Null** or empty.

####Left

To accomplish this we need to write an **Outer Join**. To write an **Outer Join** we need to decide which table do we want to get all the data from. We specify the table we want to get all the data from by indicating it as either **Left** or **Right**.

```
Select * from Sales.Customer a
left join Sales.SalesOrderHeader b
on a.CustomerID = b.CustomerID
```

In the statement above, **Left** refers to the **Sales.Customer** table. Which means we want to get all the data from **Sales.Customer** and if there are any matching records in the **Sales.SalesOrderHeader** table return those records, otherwise leave those records as **Null**.

![left-join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q679idn76r7vqdra9d2o.gif)

###Right

The **Right** method works exactly like the **Left**, except it refers to the table on end of the query statement. In our example above it would refer to **Sales.SalesOrderHeader** table.

![right-method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xqdkt9oqmyfos5odosuj.gif)

That concludes this series on Learn SQL. I must say I was intimidated by SQL when I first heard of it back in 2020. 

After going on this journey I have realised my error in judgement and found myself understanding the concepts quite easily, since they resemble javascript in many ways, thankfully not all ways.

Once again I hope you enjoyed this series and have a great 2022. Namaste.

![Yoga](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a3719v1wx1c7oa9izye1.gif)