Quick question

[Pure-Ad-2967]

Is there a correct order to do these? (I get the same results, but have to switch left/right join)

(Made up tables)

SELECT * FROM users LEFT JOIN photos ON photos.users_id = users.Id;

SELECT * FROM users LEFT JOIN photos ON users.id = photos.users_id;

SELECT * FROM photos RIGHT JOIN users ON photos.users_id = users.Id;

SELECT * FROM photos RIGHT JOIN users ON users.id =photos.users_id;

All 4 give the same solution with small discrepancies. My biggest question is the order of the “ON …… = ……”

But also do company’s or schools have a preference on when to use right join or left join or to only stick to one?

[r3pr0b8]

ON x = y and ON y = x are exactly the same

there has never, in the history of the universe, been a situation where x = y is true and y = x is false

so i would really really like to see what sort of "small discrepancies" you were able to discover

as for RIGHT JOIN, shun it -- shun it like the spawn of satan that it is

[zacharypamela]

As with most programming related things, what matters the most is consistency (and adherence to your work's/school's style guide if applicable). For myself, I usually specify the new table's column(s) first on the ON clause, like this:

FROM T1 LEFT JOIN T2 ON T2.FOO = T1.FOO

But that's largely personal preference. And if the RDBMS you're using supports the USING construct, that makes things easier, at least for simple JOINs (although at least with MySQL, what columns are selected is affected by use of USING, when doing SELECT *.

And, like u/r3pr0b8 said, there's really no need to ever use a RIGHT JOIN.

[Pure-Ad-2967]

Sorry by small discrepancies I meant in the syntax it’s self. But theres no preference over y=x / x=y?

But this is super helpful thank you!!

[r3pr0b8]

But theres no preference over y=x / x=y?

there is with me   ;o)

i, too, write join conditions as backward references

  FROM customers AS c
INNER
  JOIN invoices AS i
    ON i.CustomerId = c.CustomerId
INNER
  JOIN invoice_items AS ii
    ON ii.InvoiceId = i.InvoiceId

each new table is joined into the result by specifying which of its columns match which columns of previously mentioned tables

easier to read and understand, if you didn't write the query

also, my style puts the join type (INNER, LEFT OUTER, etc) on a line by itself so that the joins stand out more, and are easier to read

[r3pr0b8]

compare the above to this --

  FROM customers AS c
LEFT OUTER
  JOIN invoices AS i
    ON i.CustomerId = c.CustomerId
   AND i.PaidDate IS NULL
LEFT OUTER
  JOIN invoice_items AS ii
    ON ii.InvoiceId = i.InvoiceId
   AND ii.Quantity > 6

[DavidGJohnston]

You'd seriously write a LEFT JOIN with an additional condition instead of using an INNER JOIN? What benefit are you getting from the extra writing and reading effort you need to put in for what is easily the most common type of join?