MySQL LEFT OUTER JOIN performance on a local column condition Assume the following tables, indexed on the obvious keys:

Table users
------------------------
id | name | has_cars
------------------------
1    Dan    0
2    Bob    0
3    Jake   1
4    Jen    1

Table cars
------------------------
id | user_id | description
------------------------
1    3         Corvette
2    3         Viper
2    3         Lamborghini
2    4         Camry


Is there any performance benefit to the latter of the following two queries?

SELECT users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON cars.user_id = users.id


vs

SELECT users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON users.has_cars &gt; 0
AND cars.user_id = users.id


These tables, are of course for sake of example. I'm curious about the implications on much larger tables with very sparse has_cars &gt; 0 condition-meeting-rows. Is the optimizer able to take advantage of using a row-local condition to determine whether or not to attempt the LEFT OUTER JOIN per row?

EDIT: Adding another potential use case to add legitimacy to the question.

Here's a potential use case: let's say you have a table with a type column. For type a rows you want to join the type_a table data, for type b rows you want to join the table_b table data, etc, and here a local condition would determine whether or not to perform a LEFT OUTER JOIN:

SELECT widgets.*, a_widgets.*, b_widgets.*, c_widgets.*
FROM widgets
LEFT OUTER JOIN a_widgets ON widgets.type = 'a' AND a_widgets.widget_id = widgets.id
LEFT OUTER JOIN b_widgets ON widgets.type = 'b' AND b_widgets.widget_id = widgets.id
LEFT OUTER JOIN c_widgets ON widgets.type = 'c' AND c_widgets.widget_id = widgets.id


For each table join, are you getting any performance benefit from the added local condition (widgets.type = _)? As compared to:

SELECT widgets.*, a_widgets.*, b_widgets.*, c_widgets.*
FROM widgets
LEFT OUTER JOIN a_widgets ON a_widgets.widget_id = widgets.id
LEFT OUTER JOIN b_widgets ON b_widgets.widget_id = widgets.id
LEFT OUTER JOIN c_widgets ON c_widgets.widget_id = widgets.id


(Assuming there is a widgets.type column, and if a row's type is a,b, or c there is a corresponding row in the a_widgets, b_widgets or c_widgets table.)
It is not 'proper' to have redundant information in a database.  has_cars is redundant with EXISTS(SELECT 1 FROM cars WHERE user_id = ...).

So, once you eliminate has_cars, you are left with just a third option:

SELECT users.*, cars.*
    FROM users
    JOIN cars  ON cars.user_id = users.id


which is the preferred query.

Do not use LEFT unless you are hoping to 'find' rows missing from the right-hand table.  In that case, use the LEFT JOIN ... IS NULL pattern:

SELECT users.name  'Does not have any cars'
    FROM users
    LEFT JOIN cars  ON cars.user_id = users.id
    WHERE cars.id IS NULL;          -- to list _only_ those without cars


Or to get data from cars whether or not there is a row:

SELECT users.name,
       cars.description  -- will be NULL if no cars
    FROM users
    LEFT JOIN cars  ON cars.user_id = users.id;


Another variant:

SELECT users.name,
       COUNT(cars.id)  AS 'how many cars owned (incl 0)'
    FROM users
    LEFT JOIN cars  ON cars.user_id = users.id
    GROUP BY users.id;


This is equivalent to that:

SELECT name,
       ( SELECT COUNT(cars.id) FROM cars  WHERE user_id = users.id
             )  AS 'how many cars owned (incl 0)'
    FROM users;


See also GROUP_CONCAT() as a way of listing (in a single cell) all the cars owned by each person.