Matthew.learn Matthew.learn - 2 months ago 5
SQL Question

Discrepancy in SQL result when joining tables

I'm trying to write a query that will return all movies in SAKILA database that have french as the original language. The expected result is 0 rows.

When I use

language_id
to find titles with french as the language, I get the expected result:

SELECT title, release_year FROM sakila.film WHERE language_id =5;


However,
I'm trying to get this result without using literals
(language_id)
, and instead joining the two tables to select the film's original language.

When I write:

SELECT title, release_year
FROM sakila.film, sakila.language
WHERE language.name = 'french';


I get all rows.

Answer

In case you don't want to use join, you need to add one condition inside where.

SELECT title, release_year
FROM sakila.film, sakila.language
WHERE language.name = 'french' and film.language_id = language.id