同时加入操作和限制 [英] Join operation and restrictions at the same time

问题描述

我们可以同时使用join操作和做限制吗?我想基于来自另一个数据库(在示例中称为 DB2)的两个表的数据库(在示例中称为 DB1)上创建一个表，其中当另一列(在例如性别"列中的4").-> 如果是，那怎么办?

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 ?

两个数据库在同一台服务器上，DBMS 是一样的.ID1和ID2基于DB2中的table1；ID1 和名称基于 DB2 中的 table2.

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.

这是我已经尝试过的，但我不确定它是否有效和/或从我使用 DB1 的角度来看它是否是最好的方法:

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; 

或者使用 AND 是否正确(更好)?:

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; 

推荐答案

它们是等价的(只要小心表别名 ..anyway )

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

你可以直接在 on join 子句中使用 and 条件

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

或在 join 子句后使用 where

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

这篇关于同时加入操作和限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！