Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been reading up a bit regarding JOINs. And i just noticed that I've been writing the syntax a little bit different. Although I don't think there's any problem with it, I would like to hear your oppinions.

This is how I see just about every tutorials writes the syntax:

SELECT a.<columns>
    b.<columns> 
FROM table_a a 
    INNER JOIN table_b b ON a.pk = b.fk_table_a_pk

And this is how I've been doing it:

SELECT a.<columns>
    b.<columns> 
FROM table_a a 
    JOIN table_b b ON b.fk_table_a_pk = a.pk

As you can see; I've switched around the part where the comparison takes place.
Instead of a.pk = b.fk_table_a_pk, I do b.fk_table_a_pk = a.pk. I don't know why, but I would guess it's because it made more sense - in my head - to continue "working" on the joined table (JOIN table_b b ON b.fk_table_a_pk =)

I've always gotten the expected result, so I've never thought about it. But is this wrong? Or is there situations, that I'm not aware of yet, where this could fail and return an unexpected result?

An additional question regarding LEFT and RIGHT joins.
Is it correct to say that the joined table (table_b) is the RIGHT table? - and that table_a is the LEFT table - in this case?
And if I continue the code like this:

....
FROM table_a a 
    JOIN table_b b ON b.fk_table_a_pk = a.pk
    JOIN table_c c ON c.fk_table_a_pk = a.pk

they're both RIGHT tables relative to table_a? And if I continue like this:

    ....
    JOIN table_c c ON c.fk_table_a_pk = a.pk
        JOIN table_d ON d.fk_table_c_pk = c.pk

now table_c is still RIGHT relative to table_a, but is now also LEFT relative to table_d - and of course then table_d is RIGHT relative to table_c - in this case?

share|improve this question
    
You haven't any LEFT or RIGHT joins in your code. Only (INNER) JOINs. –  ypercube Jan 14 at 18:44
    
Yes, I know. I just needed to know if I've understood the relation between tables if I added LEFT or RIGHT to the JOIN. Which table was which... –  ThomasK Jan 14 at 18:49

2 Answers 2

The = sign represents the symmetric equal operator. It does not matter in which order the to sides are mentioned, they are still equal.

As for the LEFT and RIGHT JOIN, the table order is important here. As you stated correctly, it is:

...FROM LeftTable LEFT JOIN RightTable ON...

...FROM LeftTable RIGHT JOIN RightTable ON...

The LEFT JOIN returns all rows of the LeftTable regardless of a match, the RIGHT JOIN returns all rows of the RightTable regardless of a match.

For more information on how JOINs work, check out my article series here: http://sqlity.net/en/1146/a-join-a-day-introduction/

It is written for Microsoft SQL Server but most of the concepts are RDBMS independent.

share|improve this answer
    
So I've got it correct then. The joined table will always become the RIGHTtable. And by using LEFT- or RIGHT JOIN would determine which one of them to fetch all records regardless of match in the opposite one... –  ThomasK Jan 14 at 19:14
    
That is correct. –  Sebastian Meine Jan 14 at 20:31
    
One other thing regarding parent and child tables. In you RIGHT OUTER JOIN article you mentions that your script asks for "all Parent records and only matching Child records". And in your script the parent table is the one that is getting joined. Is it allways like that? or how do I know which one should be treaded as a parent and which should be treaded as the child table? –  ThomasK Jan 14 at 22:31
    
Parent and child relationships are usually determined by the data in the table and not by the type of join or the order of the tables within. If you have a Person table and an Address table, a person would be the parent and all its addresses would be the children. –  Sebastian Meine Jan 15 at 0:51

In the equality conditions, I like to list the tables in the same order they appear in the query. That's just personal preference, and from a technical standpoint, it makes no difference if you swap the sides of the = operator. Obviously, for more complex (in)equality statements, or conditional expressions, you may be forced to improvise a bit.

For outer joins, the LEFT vs. RIGHT designation is simply to allow telling the DBMS which 'side' of the join should have all records returned regardless of no matches existing. A full outer join includes all records from both sides. The table listed first is considered 'left', and the table listed second is considered 'right'.

Potential outer join pitfall:

SELECT *
FROM a
    LEFT OUTER JOIN b
        ON a.a_id = b.a_id
    INNER JOIN c
        ON b.b_id = c.b_id

This effectively turns your outer join into an inner join, as the join between b and c is not optional. You would likely want to write the expression like this, joining records in a to records in b, but only if they match a record in c:

SELECT *
FROM a
    LEFT OUTER JOIN (
        b
        INNER JOIN c
            ON b.b_id = c.b_id
    )
        ON a.a_id = b.a_id

Using parentheses allows you to logically evaluate the inner (required) join before the optional outer join, preventing undesired filtering of records.

share|improve this answer
    
is JOIN equal to INNER JOIN, and LEFT JOIN equal to LEFT OUTER JOIN? –  ThomasK Jan 14 at 21:58
    
Yes. You will sometimes see these expressed as a [INNER] JOIN b and a LEFT [OUTER] JOIN b. –  Michael - sqlbot Jan 15 at 12:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.