Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Basics in MS SQL Server

How to query more than one table
4. Join tables on a condition
Creating JOINs
Referencing columns
Let's practice

Instruction

Surprised? If there are eight movies and five directors, most people will say that we'll get 5, 8 or 13 rows in the result. None of these are true.

We get 40 rows in total because T-SQL takes every single movie and joins it with every possible director. So we now have 8 * 5 = 40 rows!

Why did this happen? T-SQL doesn't know what to do with the results from the two tables, so it gave you every possible pair of rows. How can we change this behavior? Take a look:

SELECT
  *
FROM Person, Car
WHERE Person.Id = Car.OwnerId;

We've set a new condition in the WHERE clause. We now see only those connections where the Id from the Person table is the same as the OwnerId from the Car table. Makes sense, right?

Take a closer look at how we provide the information about columns in the WHERE condition. If you have multiple tables, you should refer to specific columns by giving the name of the table and the column, separated by a dot (.). This helps avoid ambiguity, in case two tables have common column names. In this notation, the OwnerId column from theCar table is denoted as Car.OwnerId. And so on.

Exercise

Select all columns from the tables Movie and Director such that each movie is shown together with its director.

Stuck? Here's a hint!

Type:

SELECT
  *
FROM Movie, Director
WHERE Director.Id = Movie.DirectorId;