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