Clarification - Difference in Syntax between JOIN for 2 tables each with an identical column name, and 2 tables with different column names but with identical data

Screen Link:
https://app.dataquest.io/m/463/joining-data-in-sql/1/introducing-joins

My Code:

SELECT * 
FROM facts
INNER JOIN cities ON cities.facts_id = facts.id
LIMIT 10;

What I expected to happen:
Okay, so this question is less about what I expected to happen and more about differences in syntax for different situations. I completed the SQL Fundamentals course and I saw that the syntax for joining 2 tables, each with an identical column name, was as below:

SELECT *
FROM table1
JOIN table2 ON table1.matchingcolumn = table 2.matchingcolumn;

Now, in this lesson, the column names are different but the data is basically the same, i.e. facts.id is the same data as cities.facts_id . Right? But I notice the syntax is the same as above except on the JOIN clause, it goes

JOIN table2 ON table2.column_name2 = table1.column_name1;

Would I be correct to explain to myself, as a matter of practice, that this syntax is different because the column names the 2 tables are being joined on do NOT share the same name? Or is there a different reason?

Thanks!

Yes joining same name columns from different tables is both a convenience when writing the join and slight inconvenience when SELECT later.
If you just do SELECT same_col_name..., this errors:
Column 'same_col_name' in field list is ambiguous , solution is SELECT table1.same_col_name or SELECT table2.same_col_name. SELECT * will just give you 2 copies of the same column.

Using same column name helps the user see which tables are joinable more easily. MySQL and Postgres allows you to use the USING clause to join if joining columns are same name.
If using pandas, it lets you merge using on parameter, instead of having to specify left_on and right_on.

1 Like