Mysql Using JOINs and WHERE statment. What is this difference?

Question

What is the difference bettween

SELECT a.AccountId FROM Accounts AS a JOIN domain as d;

and

SELECT a.AccountId
FROM Accounts AS a JOIN domain as d WHERE a.AccountId=d.AccountId;

I thought JOIN is an inner join which only matches when the left AccountId is == to the right AccountId. Wouldn't the above be exactly the same? I got different results when typing it into MySQL (command line).

Answer 1

Your first statement doesn't specify any join criteria at all, to make it equivalent you would need to change it to:

SELECT a.AccountId FROM Accounts AS a 
JOIN domain as d on a.accountid = d.accountid;

I think what your asking, but not sure, is whats the difference between old style joins

 SELECT a.AccountId FROM Accounts AS a, domain as d 
 where  a.accountid = d.accountid;

And ANSI joins which is

  SELECT a.AccountId FROM Accounts AS a 
  JOIN domain as d on a.accountid = d.accountid;

And those are equivalent

Answer 2

In the first example there is no criteria in the join, so all rows in A will associate with all rows in D forming 'A x D' rows - the so-called 'Cartesian join'.

In the second example only rows in A and D with the same AccountId will be listed.

Answer 3

I am not exactly sure how MySql handles unqualified joins but I am joining to guess it just did an outer join giving you tons of results for the first one. The second one qualified the join and gave you what you wanted.

In SQL Server, you would use an "on" qualifier...

Select a.AccountID from Accounts a join Domain d on a.AccountID = d.AccountID

This limits the join and makes for a much better query. MySql should have something very similar.

Answer 4

The first query without any JOIN condition will return the same results as CROSS JOIN. That means that it will JOIN every row from table Accounts with every row in table Domain. So if you have 4 rows in first table and 8 in another, you will have 32 results. If you want to take only a.AccountId it will be duplicated as many times as many rows you have in domain table. In fact I don't see the point of the first query. You should just do this:

SELECT a.AccountId FROM Accounts

The second query behaves like the first with this difference that it returns only rows which match the condition a.AccountId=d.AccountId. But you should really rewrite this query to:

SELECT a.AccountId 
FROM Accounts AS a 
JOIN domain as d 
ON a.AccountId=d.AccountId;

After ON you specify the JOIN conditions.

Answer 5

DISCLAIMER: Don't have MySQL handy, so I'm just going by what I remember.

Since you're not specifying a join condition in the first case, I believe your first line is equivalent to

SELECT a.AccountID FROM Accounts a, Domain d;

which will return a * d rows as a Cartesian product.

Your second line specifies that rows in table Accounts should match a corresponding row in Domain, so that query will return fewer rows.

Answer 6

"I thought JOIN is an inner join which only matches when the left AccountId is == to the right AccountId. Wouldn't the above be exactly the same?"

The ALGEBRAIC OPERATOR called "NATURAL JOIN" behaves this way.

Unfortunately, SQL is not the same thing as the relational algebra.

Unfortunately, you need to do additional writing ("ON A.Column=B.column") before any SQL system is willing to accept the horrible idea that natural join is indeed what you need.