Join table on a condition

Surprised, huh? If there are 8 movies and 5 directors, most people will say that we'll get 5, 8 or 13 rows in the result. This is not true.

We've got 40 rows altogether because SQL takes every single movie and joins it with every possible director. So we now have 8 * 5 = 40 rows!

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

SELECT * FROM person, car
WHERE person.id = car.owner_id;
We've set a new condition in the WHERE clause. We now see only those connections where id from person is the same as owner_id from car. 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 (.). As a result, the column owner_id from the table car becomes car.owner_id and so on.

Exercise

Select all columns from tables movie and director in such a way that a movie is shown together with its director.


Execute below Query

SELECT * FROM movie, director WHERE movie.director_id = director.id

 


  

  

ID

  

  

TITLE

  

  

PRODUCTION_YEAR

  

  

DIRECTOR_ID

  

  

ID

  

  

NAME

  

  

BIRTH_YEAR

 

  

  

1

  

  

Psycho

  

  

1960

  

  

1

  

  

1

  

  

Alfred Hitchcock

  

  

1899

 

  

  

2

  

  

Saving Private Ryan

  

  

1998

  

  

2

  

  

2

  

  

Steven Spielberg

  

  

1946

 

  

  

3

  

  

Schindler's List

  

  

1993

  

  

2

  

  

2

  

  

Steven Spielberg

  

  

1946

 

  

  

4

  

  

Midnight in Paris

  

  

2011

  

  

3

  

  

3

  

  

Woody Allen

  

  

1935

 

  

  

5

  

  

Sweet and Lowdown

  

  

1993

  

  

3

  

  

3

  

  

Woody Allen

  

  

1935

 

  

  

6

  

  

Pulp fiction

  

  

1994

  

  

4

  

  

4

  

  

Quentin Tarantino

  

  

1963

 

  

  

7

  

  

Talk to her

  

  

2002

  

  

5

  

  

5

  

  

Pedro Almodóvar

  

  

1949

 

  

  

8

  

  

The skin I live in

  

  

2011

  

  

5

  

  

5

  

  

Pedro Almodóvar

  

  

1949

 

Select from multiple table

We know who directed a specific movie because there is a column director_id in the table movie. If you take a look at "Midnight in Paris", its director_id is 3. So we can now look into directors to find out that id 3 is assigned to Woody Allen. And that's how we know he is the director. Did you get that right?

There are quite a few ways of getting information from multiple tables at the same time. We're going to start with the easiest one.

SELECT * FROM person, car;

Syntax :

SELECT * FROM table1, table2;

You already know how SELECT * FROM works, don't you? Now we just name two tables instead of one, and we separate them with a comma (,). Piece of cake! The result, however, might be a tiny bit of a surprise to you. Let's check that out.

Excercide-

Select all data from table movie and table director;

SELECT * FROM movie, director;

ID	TITLE	PRODUCTION_YEAR	DIRECTOR_ID	ID	NAME	BIRTH_YEAR
1	Psycho	1960	1	1	Alfred Hitchcock	1899
1	Psycho	1960	1	2	Steven Spielberg	1946
1	Psycho	1960	1	3	Woody Allen	1935
1	Psycho	1960	1	4	Quentin Tarantino	1963
1	Psycho	1960	1	5	Pedro Almodóvar	1949
2	Saving Private Ryan	1998	2	1	Alfred Hitchcock	1899
2	Saving Private Ryan	1998	2	2	Steven Spielberg	1946
2	Saving Private Ryan	1998	2	3	Woody Allen	1935
2	Saving Private Ryan	1998	2	4	Quentin Tarantino	1963
2	Saving Private Ryan	1998	2	5	Pedro Almodóvar	1949
3	Schindler's List	1993	2	1	Alfred Hitchcock	1899
3	Schindler's List	1993	2	2	Steven Spielberg	1946
3	Schindler's List	1993	2	3	Woody Allen	1935
3	Schindler's List	1993	2	4	Quentin Tarantino	1963
3	Schindler's List	1993	2	5	Pedro Almodóvar	1949
4	Midnight in Paris	2011	3	1	Alfred Hitchcock	1899
4	Midnight in Paris	2011	3	2	Steven Spielberg	1946
4	Midnight in Paris	2011	3	3	Woody Allen	1935
4	Midnight in Paris	2011	3	4	Quentin Tarantino	1963
4	Midnight in Paris	2011	3	5	Pedro Almodóvar	1949
5	Sweet and Lowdown	1993	3	1	Alfred Hitchcock	1899
5	Sweet and Lowdown	1993	3	2	Steven Spielberg	1946
5	Sweet and Lowdown	1993	3	3	Woody Allen	1935
5	Sweet and Lowdown	1993	3	4	Quentin Tarantino	1963
5	Sweet and Lowdown	1993	3	5	Pedro Almodóvar	1949
6	Pulp fiction	1994	4	1	Alfred Hitchcock	1899
6	Pulp fiction	1994	4	2	Steven Spielberg	1946
6	Pulp fiction	1994	4	3	Woody Allen	1935
6	Pulp fiction	1994	4	4	Quentin Tarantino	1963
6	Pulp fiction	1994	4	5	Pedro Almodóvar	1949
7	Talk to her	2002	5	1	Alfred Hitchcock	1899
7	Talk to her	2002	5	2	Steven Spielberg	1946
7	Talk to her	2002	5	3	Woody Allen	1935
7	Talk to her	2002	5	4	Quentin Tarantino	1963
7	Talk to her	2002	5	5	Pedro Almodóvar	1949
8	The skin I live in	2011	5	1	Alfred Hitchcock	1899
8	The skin I live in	2011	5	2	Steven Spielberg	1946
8	The skin I live in	2011	5	3	Woody Allen	1935
8	The skin I live in	2011	5	4	Quentin Tarantino	1963
8	The skin I live in	2011	5	5	Pedro Almodóvar	1949