Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Left Join With Where Clause

People also ask

Can we use WHERE clause in joins?

To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.

Can we use WHERE clause in left join in SQL?

However, moving the WHERE condition to the ON clause applies it to the individual tables prior to joining. This enables the left join to retain rows from the left table even though some column entries of those rows (entries from the right tables) do not satisfy the WHERE condition.

Can we use WHERE clause in left outer join?

An outer join returns all of the rows that the equivalent inner join would return, plus non-matching rows from one or both tables. In the FROM clause, you can specify left, right, and full outer joins. In the WHERE clause, you can specify left and right outer joins only.

WHERE clause before or after LEFT join?

SQL Server LEFT JOIN example As always, SQL Server processes the WHERE clause after the LEFT JOIN clause.


The where clause is filtering away rows where the left join doesn't succeed. Move it to the join:

SELECT  `settings`.*, `character_settings`.`value`
FROM    `settings`
LEFT JOIN 
       `character_settings` 
ON     `character_settings`.`setting_id` = `settings`.`id`
        AND `character_settings`.`character_id` = '1'  

When making OUTER JOINs (ANSI-89 or ANSI-92), filtration location matters because criteria specified in the ON clause is applied before the JOIN is made. Criteria against an OUTER JOINed table provided in the WHERE clause is applied after the JOIN is made. This can produce very different result sets. In comparison, it doesn't matter for INNER JOINs if the criteria is provided in the ON or WHERE clauses -- the result will be the same.

  SELECT  s.*, 
          cs.`value`
     FROM SETTINGS s
LEFT JOIN CHARACTER_SETTINGS cs ON cs.setting_id = s.id
                               AND cs.character_id = 1

If I understand your question correctly you want records from the settings database if they don't have a join accross to the character_settings table or if that joined record has character_id = 1.

You should therefore do

SELECT `settings`.*, `character_settings`.`value`
FROM (`settings`)
LEFT OUTER JOIN `character_settings` 
ON `character_settings`.`setting_id` = `settings`.`id`
WHERE `character_settings`.`character_id` = '1' OR
`character_settings`.character_id is NULL

You might find it easier to understand by using a simple subquery

SELECT `settings`.*, (
    SELECT `value` FROM `character_settings`
    WHERE `character_settings`.`setting_id` = `settings`.`id`
      AND `character_settings`.`character_id` = '1') AS cv_value
FROM `settings`

The subquery is allowed to return null, so you don't have to worry about JOIN/WHERE in the main query.

Sometimes, this works faster in MySQL, but compare it against the LEFT JOIN form to see what works best for you.

SELECT s.*, c.value
FROM settings s
LEFT JOIN character_settings c ON c.setting_id = s.id AND c.character_id = '1'

For this problem, as for many others involving non-trivial left joins such as left-joining on inner-joined tables, I find it convenient and somewhat more readable to split the query with a with clause. In your example,

with settings_for_char as (
  select setting_id, value from character_settings where character_id = 1
)
select
  settings.*,
  settings_for_char.value
from
  settings
  left join settings_for_char on settings_for_char.setting_id = settings.id;