Query with three tables

Question

select  record1.fname, record2.fname, record3.fname 
from record1, record2, record3 
where record1.country= record2.country
or record1.country=record3.country

What is the equivalent query of the above code if you will use inner join and left join? I not yet that familiar with join, but I know how to use join if there are only two table.

Answer 1

I've presented it as an inner join'd query, rather than a left join'd query. The left-join version would be identical, just replacing INNER with LEFT

SELECT r1.fname, 
       r2.fname, 
       r3.fname 
FROM   record1 r1
INNER
JOIN   record2 r2
       ON r1.country = r2.country
INNER
JOIN   record3 r3
       ON r2.country = r3.country

I've also used table aliases of r1, r2 and r3 as, at least to me, it makes the query easier to read/less "busy" than using the full names of the tables as aliases.

Answer 2

You should start using the official ANSI SQL Join syntax - with inner joins like this:

select  
   record1.fname, record2.fname, record3.fname 
from 
   record1
inner join 
   record2 on record1.country= record2.country
inner join 
   record3 on record1.country=record3.country

With this approach, it's pretty easy to switch to outer joins where needed:

select  
   record1.fname, record2.fname, record3.fname 
from 
   record1
left outer join 
   record2 on record1.country= record2.country
left outer join 
   record3 on record1.country=record3.country

So start using the explicit JOIN syntax - don't just list a bunch of tables and define the join conditions in the WHERE clause - that's deprecated, and not very clear when you read it.

Jeff Atwood (one of the guys behind this site) also has a great blog post visualizing the join types - highly recommended and very easy to understand and "get" what the types are and how they work.

Answer 3

-- INNER  
SELECT r1.fname, r2.fname, r3.fname
FROM record1 r1

    INNER JOIN record2 r2 ON r2.country = r1.country
    INNER JOIN record3 r3 ON r3.country = r2.country

-- LEFT OUTER
SELECT r1.fname, r2.fname, r3.fname
FROM record1 r1

    LEFT OUTER JOIN record2 r2 ON r2.country = r1.country
    LEFT OUTER JOIN record3 r3 ON r3.country = r2.country