Queries that implicit SQL joins can't do?

Question

I've never learned how joins work but just using select and the where clause has been sufficient for all the queries I've done. Are there cases where I can't get the right results using the WHERE clause and I have to use a JOIN? If so, could someone please provide examples? Thanks.

Answer 1

Yes. When doing outer joins. You can read this simple article on joins. Joins are not hard to understand at all so you should start learning (and using them where appropriate) right away.

Answer 2

Any time you want to combine the results of two tables you'll need to join them. Take for example:

Users table:

ID
FirstName
LastName
UserName
Password

and Addresses table:

ID
UserID
AddressType (residential, business, shipping, billing, etc)
Line1
Line2
City
State
Zip

where a single user could have his home AND his business address listed (or a shipping AND a billing address), or no address at all. Using a simple WHERE clause won't fetch a user with no addresses because the addresses are in a different table. In order to fetch a user's addresses now, you'll need to do a join as:

SELECT *
FROM Users
LEFT OUTER JOIN Addresses
    ON Users.ID = Addresses.UserID
WHERE Users.UserName = "foo"

See http://www.w3schools.com/Sql/sql%5Fjoin.asp for a little more in depth definition of the different joins and how they work.

Answer 3

Implicit join syntax by default uses Inner joins. It is sometimes possible to modify the implicit join syntax to specify outer joins, but it is vendor dependent in my experience (i know oracle has the (-) and (+) notation, and I believe sqlserver uses *= ). So, I believe your question can be boiled down to understanding the differences between inner and outer joins.

We can look at a simple example for an inner vs outer join using a simple query..........

The implicit INNER join:

select a.*, b.*
from table a, table b
where a.id = b.id;

The above query will bring back ONLY rows where the 'a' row has a matching row in 'b' for it's 'id' field.

The explicit OUTER JOIN:

select * from
table a LEFT OUTER JOIN table b
on a.id = b.id;

The above query will bring back EVERY row in a, whether or not it has a matching row in 'b'. If no match exists for 'b', the 'b' fields will be null.

In this case, if you wanted to bring back EVERY row in 'a' regardless of whether it had a corresponding 'b' row, you would need to use the outer join.

Like I said, depending on your database vendor, you may still be able to use the implicit join syntax and specify an outer join type. However, this ties you to that vendor. Also, any developers not familiar wit that specialized syntax may have difficulty understanding your query.

Answer 4

Are there cases where I can't get the right results using the WHERE clause and I have to use a JOIN?

Any time your query involves two or more tables, a join is being used. This link is great for showing the differences in joins with pictures as well as sample result sets.

If the join criteria is in the WHERE clause, then the ANSI-89 JOIN syntax is being used. The reason for the newer JOIN syntax in the ANSI-92 format, is that it made LEFT JOIN more consistent across various databases. For example, Oracle used (+) on the side that was optional while in SQL Server you had to use =*.

Answer 5

Using Joins :

SELECT a.MainID, b.SubValue AS SubValue1, b.SubDesc AS SubDesc1, c.SubValue AS SubValue2, c.SubDesc AS SubDesc2
FROM MainTable AS a
LEFT JOIN SubValues AS b ON a.MainID = b.MainID AND b.SubTypeID = 1
LEFT JOIN SubValues AS c ON a.MainID = c.MainID AND b.SubTypeID = 2

Off-hand, I can't see a way of getting the same results as that by using a simple WHERE clause to join the tables. Also, the syntax commonly used in WHERE clauses to do left and right joins (*= and =*) is being phased out,

Answer 6

Implicit joins are 17 years out of date, why would you even consider writing code with them?

Yes, they can create problems that explicit joins don't have. Speaking about SQl Server the left and right join implicit syntax are not guaranteed to return the correct results. SOmetimes they return a cross join instead of an outer join. THis is a bad thing and the = or = should never be used. This was true even back to SQL Server 2000 at least. And they are being pahsed out, so using them is an all around poor practice.

The other problem with the implicit joins is that it is easy to accidentally do a cross join by forgetting one of the where conditions, especially when you are joining to many tables. By using explicit joins, you will get a syntax error if you forget to put in a join condition and a cross join must be explicitly specified as such. Again, this results in queries that reurn incorrect values or are fixed by using distinct to get rid of the cross join which is inefficient at best.

I believe some ORMs also now require explicit joins.

Further, if you are using implied joins because you don't understand how joins operate, chances are high that you are writing code that, in fact, does not return the correct result becasue you don't know how to evaluate what the correct result would be since you don't understand what a join is meant to do.

If you write SQL code of any flavor, there is no excuse for not thoroughly understanding joins.