Writing inner joins and outer joins in SQL
Part two of a six-part series of blogs

An in-depth SQL tutorial on how to create inner joins, left outer joins, right outer joins, full outer joins, cross joins and self-joins!

  1. Inner Joins, Outer Joins and Shake-it-all-about Joins
  2. Creating Joins using your Mouse - the Query Editor (this blog)
  3. Writing Inner Joins in SQL
  4. Full, Left and Right Outer Joins in SQL
  5. Cross Joins using SQL
  6. Self-Joins in SQL (joining a table to itself)

This online training on joins is part of a longer tutorial on the whole of SQL.  Naturally, Wise Owl also run a range of SQL training courses for individualas and (above all) for businesses.

Posted by Andy Brown on 30 November 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating Joins using your Mouse - the Query Editor

If your head spins when you try to remember the syntax of joins in SQL, use this method instead!  You first need to make sure you have a new query on screen in SQL Server Management Studio:

New query button Chosoing the current database
Create a new query ... ... then choose the current database.

Let's assume that you now want to create a query showing the author's name for each book in our database:

Author and book names

The query should show the 7 books with corresponding authors.


Creating a New Query Diagrammatically

To start joining two tables together, right-click in a new query:

Design Query in Editor menu

Right-click in a new query and choose to design your query.


Now choose the tables you want to include in your query:

Choosing tables for query

Double-click on each table that you want to include in your query.

In our case, Management Studio will create a link between the tables:

Two tables joined

SQL Server automatically creates a join between the two tables.


Management Studio puts this link in because each table has a column with the same name (AuthorId) and the same data type (bigint) with one of the two tables (tblAuthor) having this column as its primary key.

You can now choose which columns you want to include, and add any sorting, filtering (and much more besides):

Designing a query in the editor

Here we've chosen to include 3 columns,  and we're about to sort the query by the LastName column.


When you've finished you can choose OK to insert the corresponding SQL into your query (although initially it won't be prettty):

SELECT tblAuthor.FirstName, tblAuthor.LastName, tblBook.BookName


tblBook ON tblAuthor.AuthorId = tblBook.AuthorId

Tidying Up the SQL Generated

Once you've got the SQL (and join syntax) that you want, you can make it look neater.  It won't affect the SQL, but it may make you feel better!

-- show authors and books








ON tblAuthor.AuthorId = tblBook.AuthorId

The above SQL is exactly the same as the code generated, give or take a bit of indentation and a comment - but it's MUCH easier to read.

Adding a Join in the Query Editor

This is all very well, but what happens if SSMS doesn't create a join for you?  

Two tables with no join line

Suppose that the tables weren't joined automatically?

In the above case, you could manually create a join by dragging one of the AuthorId columns onto the other:

Clicking and dragging to create join

Drag the AuthorId column (1) onto column (2) in the tblBook table to create a join.


Editing Joins

You can also change the nature of joins by right-clicking on them:

Right-clicking on join

Right-click on any join to see the options shown above (and explained below).

The option shown selected above (Select All Rows from tblAuthor) would create a left outer join:

A left outer join in the query editor

What a left outer join looks like in query editor.


Here's the SQL generated for this diagram:








ON tblAuthor.AuthorId = tblBook.AuthorId



However, we haven't yet seen what an inner join or outer join actually are, so it's time to step back a bit and have a look at how to create an inner join the hard way - by writing SQL.

This blog has 0 threads Add post