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 SSMS 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.
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.
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.
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.
Top comments (0)