MySQL join with where clause

by

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!

More Related Contents:

  1. How can I select data from different tables?
  2. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  3. How can I do three table JOINs in an UPDATE query?
  4. SELECTING with multiple WHERE conditions on same column
  5. MySQL: Quick breakdown of the types of joins [duplicate]
  6. Join between tables in two different databases?
  7. MySQL JOIN ON vs USING?
  8. Required to join 2 tables with their FKs in a 3rd table
  9. Why does MySQL report a syntax error on FULL OUTER JOIN?
  10. conditional join in mysql
  11. MYSQL Select MAX Date inside a join statement
  12. CROSS/OUTER APPLY in MySQL
  13. Mixing ANSI 1992 JOINs and COMMAs in a query
  14. a count for each join – optimisation
  15. MySql Count cannot show 0 values
  16. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  17. MySQL JOIN the most recent row only?
  18. MySQL update a joined table
  19. MySQL JOIN with LIMIT 1 on joined table
  20. MySQL Select last 7 days
  21. Difference between these two joining table approaches?
  22. Laravel join with 3 Tables
  23. MySQL Insert & Joins
  24. How to add a where clause in a MySQL Insert statement?
  25. How to join two tables using a comma-separated-list in the join field
  26. When to use a left outer join?
  27. In what order are MySQL JOINs evaluated?
  28. How to store a one to many relation in MySQL database?
  29. MySQL cartesian product between two SELECT statements
  30. MySQL Multiple Joins in one query?

Leave a Comment