-3
SELECT region.name, sales_reps.name, accounts.name
FROM sales_reps
JOIN region
   ON sales_reps.region_id = region.id
JOIN accounts
   ON accounts.sales_rep_id = sales_reps.id  
ORDER BY accounts.name

and

SELECT r.name region, s.name rep, a.name account
FROM sales_reps s
JOIN region r
   ON s.region_id = r.id
JOIN accounts a
   ON a.sales_rep_id = s.id
ORDER BY a.name

I get different results, the first one only returns the namee column while the other returns all of the three columns specified

G Wimpassinger
  • 751
  • 5
  • 18
Omar Sobhy
  • 29
  • 4
  • Please, [tag your DBMS](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms). It is strange behaviour, because you've requested 3 columns and DBMS should not decide to break your resultset structure even if there's repetition in names. – astentx Feb 03 '21 at 00:10
  • 3
    Is this from w3schools or something like that? I can vaguely remember there was another question like that some time ago that asked about this bug... – sticky bit Feb 03 '21 at 00:16
  • nope its from an online course I am currently attending – Omar Sobhy Feb 03 '21 at 00:19
  • 1
    So "something like that"... – sticky bit Feb 03 '21 at 00:20
  • The conclusion: 1) Always use column aliases for identically named columns. 2) Always use column qualifiers to protect your queries against addition of new columns with the same name to some table. This will not produce the need to face such bugs and will make the query more readable. – astentx Feb 03 '21 at 00:31

1 Answers1

0

The difference between both queries is the use of aliases.

You are REQUIRED to provide an alias when you reference the table multiple times and when you have derived outputs (sub-queries acting as tables).

This question has already been solved here: Alias SQL, check the given answer, is well explained.

Jesus Aguas
  • 244
  • 1
  • 7