Question 289494 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Multiple LEFT JOIN conditions vs. WHERE

Example 1:

FROM
    table1
    LEFT JOIN table2 ON
        table1.id = table2.id
        AND
        table2.flag = TRUE

Example 2:

FROM
    table1
    LEFT JOIN table2 ON
        table1.id = table2.id
WHERE
    table2.flag = TRUE

Q1: Will both of these return the same number of rows? If not, why?

Q2: Which one is better?

Tags:

3

Answers


  1. 0

    No, they will not produce(might not) same result.
    As soon as you use a column from your LEFT JOINed table in where clause it acts like the LEFT part will not be considered.

    As per your 2nd question, Which one is better, This is not a matter of better or worse, it totally depends on what your need of result is.

    Here is an example

    Create table1

    CREATE TABLE `table1` (
  `main_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(200) DEFAULT NULL,
  `flag` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`main_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

insert  into `table1`(`main_id`,`first_name`,`flag`) values (1,'FIRST',1),(2,'SECOND',1),(5,'FIFTH',1);

CREATE TABLE `table2` (
  `main_id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(200) DEFAULT NULL,
  `flag` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`main_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

insert  into `table2`(`main_id`,`last_name`,`flag`) values (2,'THIRD',0),(4,'FORTH',1);

    Your schema is now ready, Now try the following query:

    Query 1

    SELECT 
  * 
FROM
  table1 t1 
  LEFT JOIN table2 t2 
    ON t1.`main_id` = t2.`main_id` 
    AND t2.`flag` = FALSE;
RESULT:


main_id  first_name    flag  main_id  last_name    flag  
-------  ----------  ------  -------  ---------  --------
      1  FIRST            1   (NULL)  (NULL)       (NULL)
      2  SECOND           1        2  THIRD             0
      5  FIFTH            1   (NULL)  (NULL)       (NULL)

    SELECT 
  * 
FROM
  table1 t1 
  LEFT JOIN table2 t2 
    ON t1.`main_id` = t2.`main_id` 
WHERE t2.`flag` = FALSE 


main_id  first_name    flag  main_id  last_name    flag  
-------  ----------  ------  -------  ---------  --------
      2  SECOND           1        2  THIRD             0
    Login or Signup to reply.
  2. 0

    No, the number of rows that Example 1 and Example 2 return isn’t ensured to be equal. Even though there isn’t a match that meets the requirement in the right table, Example 1 still contains all of the rows from the left table. Whereas in case of example 2 Only the rows where the condition is met are included.

    In answer of your second question, depending on whether you want to include all rows from the left table (Example 1) or only rows where the condition is satisfied (Example 2), you can choose between Example 1 and Example 2. Make your selection depending on your unique needs.

    Hope it’s helpful 🙂

    Login or Signup to reply.
  3. 0

    The Both Queries are not equivalent.
    Because the first query will select all the records from first table and joins the rows from second table if they fulfill the joining condition. and will not skip any row from table1
    While the second query will skip all the records which do not fulfill the where clause. In this second case the join will work like an inner join. [i.e if the rows fulfilling the joining condition exist in both tables then select else skip].

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search