The SQL Join clause is one of the major components of the Select statement, which is used to pull data out of SQL Server

The Select keyword starts the statement. It’s often followed by a star (*) AKA splat as some DBAs call it.

Note: To automatically expand wildcards to the explicit columns see How to prevent performance problems and errors due to wildcards in SELECT statements

This just means return all columns. If we have multiple tables, a Select star will grab all columns from all tables, for example, when joining multiple tables together using the SQL Join clause which is the main subject of this article.

Let’s start with the definition. Joining is the process of taking data from multiple tables and putting it into one generated view. So, an SQL Join clause in a Select statement combines columns from one or more tables in a relational database and returns a set of data.

The From is also an essential part of the Select statement and this is where it’s specified which table we’re pulling data from. The joining part is if we want to bring data from multiple tables and we have three different types of joins:

• Inner join – this is a default. If we don’t specify a type of join, it’s going to default as the inner join. This means if we are joining two tables on a common column, only return the data that matches on both tables

• Left join – This type of join means returns all data on the left-handed table and only the data that matches the left-handed table in a right-handed table

• Right join – this type of join is the opposite of the above. It just means return data from the right-handed table and only the data that matches the left-handed table

Select using Inner Join

Let’s head over to SQL Server Management Studio (SSMS) and see how we can work with SQL Join clause using real-world examples. Below is an example of how to join tables together on a common column. In our sample AdventureWorks2012 database, we have the “Product” table that has a “ProductID” column and in the “SalesOrderDetail” table, we also got a “ProductID” column. So, if we want to find out total sales and the discounts for each product and pull the name out we must join these two together on this common column:

Note that if you just specify Join by itself, without an Inner keyword in SQL Join clause it will still be an Inner join. You can, of course, put the keyword Inner for clarity sake but if there’s no a left/right tag join it will just default to an Inner join:

Select using Left Join

Now, let’s take a look at Left Outer Join that gives us everything from the left-handed table and only the records that match in the right-handed table. In our example, the query below will give us some people that haven’t made any purchases:

The Left join returns all the records, even if they don’t exist, and it puts a Null value if it doesn’t exist:

The result set indicates that 19972 records are returned, and we have a bunch of Nulls under the “BusinessEntityID” column. The rows that have Nulls are people that haven’t made any purchases.

We can extend the above query and add another SQL Join clause to include people with the credit card information. Note that we just specified the Join keyword which is an Inner join by default and it will chop out all the Nulls because those people don’t have credit card information:

This time, the query returns 19118 records instead of previous 19972:

This is how SQL Join with a Left tag can help us out. If we want to include the records and have the full count of people even if they haven’t made purchases just Left join the second clause:

We can see that everyone that hasn’t made a purchase has a Null record for the “CreditCard” table and “PersonCreditCard” table since we left joined them:

Furthermore, we can single out all of those people who haven’t made a purchase by expanding SQL Join clause even more and searching for Null values with a Where clause:

If we run this, we get 854 records or as mentioned above, everyone who haven’t made a purchase:

So, this was an example of a left join used to create sales sheet in which we absolutely wanted every single purchase whether it has a purchase or not, but we always wanted the records to show. The key difference between the Inner joined and Left join is that we are not losing/missing records.

Select using Right Join

Now, the SQL Join clause with Right joins are the exact opposite of the Left join. They basically do the same thing. Left is right and right is left and the same effect can be rendered by just flipping the tables. Right joins are in no way deprecated, they are just not all too common. For consistency’s sake, it is a common practice to use Left joins instead of Right joins.

Conclusion and common practices

We’ve covered the three main joins: inner, left, and right join. Those three are the ones that are used most extensively. And even if you’re a newbie in the database world it’s very likely that you’ve come across different types of SQL Joins quite a bit.

In addition to this, you’ll also see Outer and Cross types of joins. Outer join has 3 different types:

1. Left outer join – fetches records if present in the left table
2. Right outer join – fetches records if present in the right table
3. Full outer join – fetches records if present in either of the two tables

4. Cross join – as the name suggests, does [n X m] that joins everything to everything. Like a scenario where we simply list the tables for joining (in the From clause of the Select statement), using commas to separate them

Points to be noted:

• If you just specify Join, then by default it is an Inner join
• An Outer join must be Left/Right/Full. You cannot just say Outer join and leave it there
• You can drop the Outer keyword and just say Left join, Right join or Full join

I hope the use of Venn diagrams to explain types of join are helpful, but they don’t always quite match the SQL join syntax reality in my testing. Therefore, I highly suggest that you don’t rely on them entirely and to experiment by writing queries and comparing the results.

I hope this article has been informative for you and I thank you for reading.

• Author
• Recent Posts

Bojan Petrovic

Bojan aka “Boksi”, an AP graduate in IT Technology focused on Networks and electronic technology from the Copenhagen School of Design and Technology, is a software analyst with experience in quality assurance, software support, product evangelism, and user engagement.

He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.

Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.

See more about Bojan at LinkedIn

View all posts by Bojan Petrovic

Latest posts by Bojan Petrovic (see all)

• Visual Studio Code for MySQL and MariaDB development - August 13, 2020
• SQL UPDATE syntax explained - July 10, 2020
• CREATE VIEW SQL: Working with indexed views in SQL Server - March 24, 2020