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:
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 the
Car table is denoted as
Car.OwnerId. And so on.