LEFT JOIN query not returning all rows in first table

by

Move the b condition from WHERE to ON to get a real LEFT JOIN. (With the b condition in the WHERE clause, it executes as a regular inner join…)

select a.id, a.name, b.store, b.stock
from products a left join stock b
  on a.id = b.id and b.store="001"
order by a.id

More Related Contents:

  1. Select rows which are not present in other table
  2. Left Join With Where Clause
  3. Two SQL LEFT JOINS produce incorrect result
  4. SQL to LINQ with multiple join, count and left join
  5. dplyr left_join by less than, greater than condition
  6. Difference between left join and right join in SQL Server [duplicate]
  7. Query with LEFT JOIN not returning rows for count of 0
  8. Are “from Table1 left join Table2” and “from Table2 right join Table1” interchangeable?
  9. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  10. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  11. Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL [duplicate]
  12. SQL SELECT from multiple tables
  13. SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword
  14. Select first record in a One-to-Many relation using left join
  15. Get Parent with multiple children in desc order
  16. How can I schedule a job to run a SQL query daily?
  17. Convert Month Number to Month Name Function in SQL
  18. How do I do top 1 in Oracle? [duplicate]
  19. Delete from two tables in one query
  20. Removing leading zeroes from a field in a SQL statement
  21. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  22. sql ORDER BY multiple values in specific order?
  23. ColdFusion adding extra quotes when constructing database queries in strings
  24. NOT NULL constraint over a set of columns
  25. Dynamic Pivot Table in SQL Server
  26. How can I determine the status of a job?
  27. How can I select rows with most recent timestamp for each key value?
  28. postgresql – sql – count of `true` values
  29. Is it possible to execute a text file from SQL query?
  30. SELECT query with CASE condition and SUM()

Leave a Comment