Join operation and restrictions at the same time

Question

Can we use the join operation and make restrictions at the same time? I want tocreate a table on a Database (called DB1 in the example) based on two tables from Database another database (called DB2 in the example) where one of the columns is filled when there´s a specific entry in on other column (in the example a"4" in column "gender"). -> If yes, then how to do ?

Both databases are on the same server and DBMS is the same. ID1 and ID2 based on table1 in DB2; ID1 and Name based on table2 in DB2.

Here´s what I tried out already but I´m not sure if it works and/or its the best way Made from point of view that I´m using DB1:

INSERT INTO table3 
            (id1, 
             id2, 
             NAME) 
SELECT t1.id1, 
       t1.id2, 
       t2.NAME 
FROM   db1.table1 t1 
       LEFT JOIN db1.table2 t2 
              ON t1.ID1=t2.ID1 
WHERE  gender = 4;

Or is it correct (better) with AND?:

INSERT INTO table3 
            (id1, 
             id2, 
             NAME) 
SELECT t1.id1, 
       t1.id2, 
       t2.NAME 
FROM   db1.table1 t1 
       LEFT JOIN db1.table2 t2 
              ON t1.ID1=t2.ID1 
                 AND gender = 4;

Is gender a t1 or t2 column? Makes a huge difference when left join... — jarlh, Oct 11 '16 at 07:37
I don´t understand what you mean - can you explain what you mean? — AbsoluteBeginner, Oct 11 '16 at 07:40
With t2.gender condition in the ON clause t1 rows with no t2 hit will be returned. With t2.gender in the WHERE clause, the left join becomes an inner join, and only t1 rows with t2 hits will be returned - where t2.gender = 4. — jarlh, Oct 11 '16 at 07:43

ScaisEdge · Accepted Answer · 2016-10-11T08:03:33.707

0

They are equivalent (just be carefull with the table alias ..anyway )

You can use the and condition directly in on join clause

    INSERT INTO table3 
        (id1, 
         id2, 
         NAME) 
      SELECT t1.id1, 
             t1.id2, 
             t2.NAME 
      FROM   db1.table1 t1 
             LEFT JOIN db1.table2 t2 
                    ON t1.ID1=t2.ID1  and t1.gender = 4

or use where after the join clause

    INSERT INTO table3 
        (id1, 
         id2, 
         NAME) 
      SELECT t1.id1, 
             t1.id2, 
             t2.NAME 
      FROM   db1.table1 t1 
             LEFT JOIN db1.table2 t2 
                    ON t1.ID1=t2.ID1  
      WHERE  t1.gender = 4;

http://dev.mysql.com/doc/refman/5.7/en/left-join-optimization.html

edited Oct 11 '16 at 08:03

answered Oct 11 '16 at 07:47

ScaisEdge

131,976
10
91
107

ok - but whats better (faster)? And what´s about this comment? "With t2.gender condition in the ON clause t1 rows with no t2 hit will be returned. With t2.gender in the WHERE clause, the left join becomes an inner join, and only t1 rows with t2 hits will be returned - where t2.gender = 4" – AbsoluteBeginner Oct 11 '16 at 07:57
The performance are the same .. because the query optimized make the two query equivalent .. for the second part .. is clear (to me) that tue use of a column in where or in and (on clause) must be esplicitally definied .. so you control the code operation but overall you avoid error for ambiguos code .. – ScaisEdge Oct 11 '16 at 08:00
I also have another question with a quit similar basis: please also take a look at:http://stackoverflow.com/questions/39977164/joins-and-restrictions-for-different-columns-at-the-same-time?noredirect=1#comment67233553_39977164 – AbsoluteBeginner Oct 11 '16 at 13:16

score 0 · Answer 2 · answered Oct 11 '16 at 08:38

This

LEFT JOIN db1.table2 t2 ON t1.ID1 = t2.ID1

is an outer join. So in case there is no match in table2 for a table1 record a dummy record gets joined with all columns set to NULL.

Then you have

WHERE t2.gender = 4

As mentioned, all outer-joined rows (i.e. table1 records that have no match in table2) have all t2 fields set to NULL, so gender is NULL and you dismiss the outer-joined row so carefully created. You could just as well use an inner join in the first place then.

For an outer join, criteria belongs in the ON clause. (And you may want to make it a habit to place it there anyway, so switching from inner joins to outer joins later is simpler.)

This

LEFT JOIN db1.table2 t2 ON t1.ID1 = t2.ID1 AND t2.gender = 4;

is an outer join where you keep the table1 record and join an empty dummy table2 record when you don't find a match with gender 4 in table2. This is most likely what you actually want.

Thanks for your help: now I get it... I also have another question with a quit similar basis: please also take a look at:http://stackoverflow.com/questions/39977164/joins-and-restrictions-for-different-columns-at-the-same-time?noredirect=1#comment67233553_39977164 — AbsoluteBeginner, Oct 11 '16 at 13:22

Join operation and restrictions at the same time

2 Answers2

Linked