SQL is this equivalent to a LEFT JoIn?

Question

Is this equivalent to a LEFT JOIN?

SELECT DISTINCT a.name, b.name 
  FROM tableA a, 
       (SELECT DISTINCT name FROM tableB) as b

It seems as though there is no link between the two tables.

Is there an easier / more efficient way to write this?

Answer 1

Not, it is equivalent to a cross or cartesian join (really bad) with a distinct applied afterwards. It is pretty hard to know what you really want with the query as it stands.

Answer 2

Isn't this the same as

SELECT DISTINCT a.name, b.name
    FROM tableA a, tableB b

although I would be questioning the purpose for this query.

Answer 3

It's ANSI-89 syntax for a cross join, producing a cartesian product (that's bad). Re-written using ANSI-92 JOIN syntax:

If on SQL Server/Oracle/Postgres, use:

    SELECT DISTINCT
           a.name,
           b.name
      FROM TABLEA a
CROSS JOIN (SELECT b.name 
              FROM TABLEB b) AS b

MySQL supports using:

SELECT DISTINCT
       a.name,
       b.name
  FROM TABLEA a
  JOIN (SELECT b.name 
          FROM TABLEB b) AS b

We'd need to know if there is any column(s) to tie records between the two tables to one another in order to update the query to use either an INNER JOIN or OUTER JOIN.

Answer 4

I hate the stigma people apply to cartesian joins. They're wonderful when used properly. I have a payroll application and we have to apply all the different taxing authorities to each employee. So, I have one table of employees and one table of taxing authorities.

Anyway.. I just wanted to defend the wonderful cartesian join. (:

</soapbox>

Answer 5

Since there is no joining field you have a cross join. The distinct limits the total number of records to remove duplicates, but probably still is not giving you the answer you want.

Do this. Check the number of records you are getting. Then write a left join joining on company name (or company id which is what you really should have as a join field as company names change frequently). I'll bet you get a different number of records returned. I did this will two tables I had handy and this is what I got: Table a had 467 records Table b had 4413 records The cross join had 2060871 The cross join with the distinct had 826804 The left join had 4712 The inner join had 893

So you can see adding the distinct to the cross join loweres the number of records returned but doesn't guarantee you will get the result you would have had with the correct join. Given that you said the tables were company and company address, it would be very unlikely that a cross join was what you wanted.