I am pretty new to SQL and I encountered something that is kind of strange in my opinion:
I have the following query:
SELECT id
FROM Worker
INNER JOIN Computer ON Worker.Id = Computer.Id
Pretty simple stuff. This query will fail though and I think that this query failing is counterintuitively.
The query will fail because I'll have 2 columns called id. I could make the query work by writing:
SELECT Worker.id
FROM Worker
INNER JOIN Computer ON Worker.Id = Computer.Id
My question is:
Why does my query require me to state what table I want the id from because
It doesn't matter from which table I get it
I pretty much stated that I want it from the 'worker' table by saying SELECT Worker.id FROM Worker
and is there a better way to define what table I want the id from besides writing SELECT Worker.id FROM Worker because I feel as if I repeated myself in a way
This is dependent on how each database provider handles their own query syntax, but here are my thoughts:
1) It doesn't matter from which table I get it
It might not matter in your example, but what about instances when the data types do not match (int/smallint, date/datetime, int/string)? Or if you are using a left/right join rather than an inner join? In these cases, the database provider would have to make a choice.
2) I pretty much stated that I want it from the 'worker' table by saying SELECT Worker.id FROM
Yes you could use the order of the tables to resolve the ambiguity. Once again this is a decision the database provider could make.
is there a better way to define what table I want the id from besides writing SELECT Worker.id FROM Worker because I feel as if I repeated myself in a way
I try to avoid using inner joins for filtering. Instead I would write the query like this:
SELECT id FROM Worker where id in (select id FROM Computer)
or
SELECT id FROM Worker as w where exists (select 1 from Computer as c where c.id = w.id)
(these may not return the same results as your query, because the inner join could cause duplicates)