This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Inner Joins, Outer Joins and Shake-it-all-about Joins Creating Joins using your Mouse - the Query Editor (this blog) Writing Inner Joins in SQL Full, Left and Right Outer Joins in SQL Cross Joins using SQL 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.

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!

Inner Joins, Outer Joins and Shake-it-all-about Joins
Creating Joins using your Mouse - the Query Editor (this blog)
Writing Inner Joins in SQL
Full, Left and Right Outer Joins in SQL
Cross Joins using SQL
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

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:


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:

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:

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

Now choose the tables you want to include in your 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:

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):

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

FROM tblAuthor INNER JOIN

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

SELECT

tblAuthor.FirstName,

tblAuthor.LastName,

tblBook.BookName

FROM

tblAuthor

INNER JOIN tblBook

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?

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:

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-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:

What a left outer join looks like in query editor.

Here's the SQL generated for this diagram:

SELECT

tblAuthor.FirstName,

tblAuthor.LastName,

tblBook.BookName

FROM

tblAuthor

LEFT OUTER JOIN tblBook

ON tblAuthor.AuthorId = tblBook.AuthorId

ORDER BY

tblAuthor.LastName

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.

Parts of this blog
Inner Joins, Outer Joins and Shake-it-all-about Joins Creating Joins using your Mouse - the Query Editor (this blog) Writing Inner Joins in SQL Full, Left and Right Outer Joins in SQL Cross Joins using SQL Self-Joins in SQL (joining a table to itself)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR