This page has 0 threads | Add post

Creating Joins using your Mouse - the Query Editor

Written by Andy Brown

In this tutorial

Creating a New Query Diagrammatically
Tidying Up the SQL Generated
Adding a Join in the Query Editor
Editing Joins

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.

Previous
tutorial

Dates and times

Previous
part

Inner Joins, Outer Joins and Shake-it-all-about Joins

Top of page

Next
part

Writing Inner Joins in SQL

Next
tutorial

Grouping data

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Joins

Creating Joins using your Mouse - the Query Editor

Creating a New Query Diagrammatically

Tidying Up the SQL Generated

Adding a Join in the Query Editor

Editing Joins

Head office

London

Manchester