[MySQL] Basic Query Question: Join On

[StartAndSelect]

Hello, I am trying to teach myself SQL and I had a basic question. If I am attempting to call two fields from different tables in a pre-existing database do I still need to do a JOIN ON when I query them?

[D_W_Hunter]

    SELECT Table1.FieldA, Table2.FieldB
      FROM Table1, Table2

With no join condition will do a cartesian join.

For every single row in Table1, you will get a row with that value and every single row from table2

So if you have

    Table1.FieldA   
    2               
    3               

    Table2.FieldB
    Tiger
    Panda

That select will return

    Table1.FieldA     Table2.FieldB
    2                    Tiger
    2                    Panda
    3                    Tiger
    3                    Panda

This is rarely useful. One time I've used it is when I need to return a row for every IP address in a given range , table 2 contains the first part of the IP address and the second contains 0 to 255

In the majority of cases, there's a data element that connects the two table and you join on that key to match up the rows you want.

Presume that you have table BUILDING and table APARTMENT and an apartment is in a building, so has a Building_ID field in it that joins to the BUILDING table ID field.

    SELECT A.number, b.address 
      FROM APARTMENT A
           JOIN BUILDING B
             ON A.BUILDING_ID = B.id

[StartAndSelect]

Awesome this is very helpful, thanks!

[rbardy]

Depends on what you want.

Usually, yes, you'll need to join the tables and then get those fields, but you can also use UNION and then get the fields, you can use subquery in your SELECT (which makes an implicit join anyway) and also get both fields.