Are these SQLite codes same/identical?

My Code:

SELECT name AS country, population
FROM facts
LEFT JOIN cities ON cities.facts_id = facts.id
WHERE cities.name IS NULL;

The code DataQuest suggests:

SELECT f.name country, f.population
FROM facts f
LEFT JOIN cities c ON c.facts_id = f.id
WHERE c.name IS NULL;

When I ran my code above, it gave this as an output:

(sqlite3.OperationalError) ambiguous column name: name
[SQL: SELECT name AS country, population FROM facts LEFT JOIN cities ON cities.facts_id = facts.id WHERE cities.name IS NULL;]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

I prefer a syntax (especially in a small code) where I write without aliases. To me my code is equivalent to the answer provided by the DataQuest. What do I miss? Aren’t the same with my code written without aliases?

Hi @huseynov.sarkhan,

Here it’s not a question of using or not aliases. The only issue with your code is that in the first line you forgot to declare the tables from which you are going to take name and population columns:

SELECT facts.name AS country, facts.population
1 Like

Thanks a lot @Elena_Kosourova.

My assumption was that as SELECT is the first line, we can introduce columns from one table only specified right after with FROM. I totally forgot that SELECT line is not executed first.

Still, it is a bit confusing to see usage of two different tables at the same time in a line regardless of its place. I’ll need to go over it to understand it better.

Cheers!

1 Like

No, when working with two or more tables it’s always necessary to specify the exact table from which each column is taken. Also, because the column names can be the same in different tables. And when the table names are too long, then yes, it’s really more convenient to use aliases :blush:

By the way, for your future questions in the Community, I recommend you to use these guidelines.

Happy coding!

1 Like

Unclashing column names do not need table name qualifiers, so not necessary but good practice for debugging where a column after join comes from.
You can try below on https://sqliteonline.com/

CREATE TABLE person
(name VARCHAR,
 petname VARCHAR);
 
CREATE TABLE pet
(name VARCHAR,
color VARCHAR);

INSERT INTO person
VALUES
('jane','kitty');

INSERT INTO pet
VALUES
('kitty','black');

SELECT petname, color  -- no error
--SELECT name   -- ambiguous column name error!
FROM person JOIN pet ON person.petname = pet.name
1 Like