MySQL join with where clause

join;MySQLwhere-clause

I have two tables I want to join.

I want all of the categories in the categories table and also all of the categories subscribed to by a user in the category_subscriptions table.

essentially this is my query so far:

SELECT *
FROM categories
LEFT JOIN user_category_subscriptions 
     ON user_category_subscriptions.category_id = categories.category_id

This works fine however I want to add a where clause on the end of the query which then essentially makes it an inner/equi join.

   SELECT *
    FROM categories
    LEFT JOIN user_category_subscriptions 
         ON user_category_subscriptions.category_id = categories.category_id 
   WHERE user_category_subscriptions.user_id = 1

How do I get all the categories as well as all the categories subscribed to by a particular user using only one query?

category_id being a key in both categories table and user_category_subscriptions. user_id residing in the user_category_subscriptions table.

thanks

Best Answer

You need to put it in the join clause, not the where:

SELECT *
FROM categories
LEFT JOIN user_category_subscriptions ON 
    user_category_subscriptions.category_id = categories.category_id
    and user_category_subscriptions.user_id =1

See, with an inner join, putting a clause in the join or the where is equivalent. However, with an outer join, they are vastly different.

As a join condition, you specify the rowset that you will be joining to the table. This means that it evaluates user_id = 1 first, and takes the subset of user_category_subscriptions with a user_id of 1 to join to all of the rows in categories. This will give you all of the rows in categories, while only the categories that this particular user has subscribed to will have any information in the user_category_subscriptions columns. Of course, all other categories will be populated with null in the user_category_subscriptions columns.

Conversely, a where clause does the join, and then reduces the rowset. So, this does all of the joins and then eliminates all rows where user_id doesn't equal 1. You're left with an inefficient way to get an inner join.

Hopefully this helps!

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

SQL JOIN – WHERE clause vs. ON clause

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

Best Answer

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

SQL JOIN – WHERE clause vs. ON clause

Related Topic