Something about the following example found on page 39 of SitePoint’s “Simply MySQL” makes no sense to me:
SELECT a, b FROM A INNER JOIN B ON a=b
From what I apparently misunderstand about this example, the SELECT clause is selecting columns ‘a’ and ‘b’ FROM table ‘A’.
Now, I understand the condition on the end (the “ON” clause, I mean) and I get the point that only rows satisfying the ON condition are returned. I understand perfectly well the INNER JOIN distinction between table ‘A’ and table ‘B’. What I can’t grasp is how the SELECT is occurring. It seems to be trying to take a non-existing ‘b’ column from table ‘A’.
I suppose this is possible with the concatenated INNER JOIN part since ‘B’ has a column ‘b’ in it…
If anyone has gone over this, I would appreciate some clarification…
And because column a is only present in table A, and column b is only present in table B, there is no confusion (in the sql engine) when the query is executed. But if there was a column called a in table B, you’d get an error message saying there was confusion (in the sql engine) as to which column a you meant.
you chose an unfortunate example, because of the oversimplification
on page 38 it says:
These tables are actually oversimplified, because they blur the distinction between table and column names. The join condition actually specifies the columns that must match. Further, it’s unusual for tables to have just one column.
to answer your question, the SELECT clause may reference any column from any table in the query
so when it says SELECT a,b the a column comes from the A table, and the b column comes from the B table
you should but dont have to specify the table.
you could have written
select tablea.a, tableb.b
on a = b