MySQL 다중 테이블 공동 쿼리 지침

1. 다중 테이블 연결 유형
1. MySQL의 데카르트 곱(교차 조인)은 CROSS JOIN이거나 JOIN인 CROSS를 생략하거나 다음과 같은 ','를 사용할 수 있습니다.

결과는 연결된 두 데이터 테이블의 곱이므로 일반적으로 WHERE, ON 또는 USING 조건이 있는 경우에는 사용하지 않는 것이 좋습니다. 데이터 테이블 항목이 너무 많으면 속도가 매우 느려지기 때문입니다. 일반적으로 LEFT [OUTER] JOIN 또는 RIGHT [OUTER] JOIN을 사용합니다

2. INNER JOIN INNER JOIN은 MySQL에서는 동등 조인이라고 합니다. 즉, MySQL에서는 CROSS에서 동등 조인 조건을 지정하고, MySQL에서는 INNER JOIN을 나누어서 지정해야 합니다. 함께. Join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

SELECT * FROM table1 CROSS JOIN table2 
SELECT * FROM table1 JOIN table2 
SELECT * FROM table1,table2

3. MySQL의 외부 조인은 왼쪽 외부 조인과 오른쪽 조인으로 나누어지며, 그 외에도 조인 조건을 지정하고, 왼쪽 테이블(왼쪽 조인)이나 오른쪽 테이블(오른쪽 조인)에서도 조인 조건을 만족하지 않는 결과를 반환하며 이에 따라 NULL이 사용된다.

예:

사용자 테이블:

id | name
———
1 | libk
2 | zyfon
3 | daodao

user_action 테이블:

user_id | action
—————
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

sql:

select id, name, action from user as u
left join user_action a on u.id = a.user_id

결과:

id | name  | action
——————————–
1 | libk     | jump      ①
1 | libk     | kick       ②
1 | libk     | jump      ③
2 | zyfon   | run        ④
3 | daodao | null       ⑤

분석:
user_action에 user_id=4, action=swim이라는 기록이 있지만 결과에는 나타나지 않습니다.
및 user 테이블 id=3, name=daodao인 사용자는 user_action에 해당 레코드가 없지만 결과 집합에 나타납니다
현재는 Left 조인이므로 모든 작업은 Left를 기반으로 합니다.
결과 1, 2, 3, 4는 왼쪽 테이블과 오른쪽 테이블에 모두 있는 레코드입니다. 5는 왼쪽 테이블에만 있고 오른쪽 테이블에는 없는 레코드입니다.

작동 원리 :

왼쪽 테이블에서 하나의 레코드 읽기에서 오른쪽 테이블에서 일치하는 모든 레코드(n)를 선택하고 연결하여 n개의 레코드를 형성합니다(결과 1과 같이 반복되는 행 포함). 결과 3) 오른쪽에 on 조건 테이블과 일치하는 레코드가 없으면 연결된 필드는 모두 null입니다. 그런 다음 계속해서 다음 항목을 읽습니다.

확장:
오른쪽 테이블에 일치 항목이 없으면 null이 표시되어 왼쪽 테이블에는 있지만 오른쪽 테이블에는 없는 모든 레코드를 찾는 규칙을 사용할 수 있습니다. 판단에 사용되는 열은 null이 아닌 것으로 선언되어야 합니다.
예:
sql:

select id, name, action from user as u
left join user_action a on u.id = a.user_id
where a.user_id is NULL

(참고:

1. 열 값이 null인 경우 = 대신 is null을 사용해야 합니다. NULL
2. 여기서 a.user_id 컬럼은 NOT NULL로 선언해야 합니다.

)

위 sql의 결과:

id | name | action
————————–
3 | daodao | NULL
 
——————————————————————————–

일반 사용법:

a. LEFT [OUTER] JOIN:

조인 조건을 충족하는 결과를 반환하는 것 외에도 왼쪽 테이블에 다음과 같은 데이터 열을 표시해야 합니다. 조인 조건을 충족하지 않는 경우

SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column
RIGHT [OUTER] JOIN:

RIGHT는 표시가 일치한다는 점을 제외하면 LEFT JOIN과 유사합니다. 연결 조건의 결과 외에도 연결 조건을 충족하지 않는 오른쪽 테이블의 데이터 열을 표시해야 합니다

SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column
팁:

1. a.c1 = b.c1은(c1)을 사용하는 것과 같습니다
2. INNER JOIN과 (쉼표)는 의미상 동일합니다
3. 테이블에서 정보를 검색할 때 선택할 인덱스를 MySQL에 묻는 메시지를 표시할 수 있습니다.
이 기능은 MySQL이 가능한 인덱스 목록 중 잘못된 인덱스를 사용하고 있음을 EXPLAIN에서 표시하는 경우 유용할 수 있습니다.
USE INDEX(key_list)를 지정하면 MySQL이 테이블에서 행을 찾기 위해 가능한 가장 적절한 인덱스를 사용하도록 지시할 수 있습니다.
선택적인 두 번째 선택 구문 IGNORE INDEX(key_list)를 사용하면 MySQL에 특정 인덱스를 사용하지 않도록 지시할 수 있습니다. 예:

mysql> SELECT * FROM table1 USE INDEX (key1,key2)
-> WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
-> WHERE key1=1 AND key2=2 AND key3=3;

2. 테이블 연결 제약 조건
WHERE, ON, USING

WHERE 절

mysql>
SELECT * FROM table1,table2 WHERE table1.id=table2.id;

2.

mysql>
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;

3. USING 절, ​​두 테이블의 조인 조건에 있는 두 열의 이름이 같은 경우 USING

을 사용할 수 있습니다. 예:

SELECT FROM LEFT JOIN USING ()

2개 이상의 테이블을 연결하는 예:

mysql>
 
SELECT artists.Artist, cds.title, genres.genre 
  
FROM cds 
  
LEFT JOIN genres N cds.genreID = genres.genreID 
  
LEFT JOIN artists ON cds.artistID = artists.artistID;

또는

mysql>
 
SELECT artists.Artist, cds.title, genres.genre 
  
FROM cds 
  
LEFT JOIN genres ON cds.genreID = genres.genreID 
  
 LEFT JOIN artists -> ON cds.artistID = artists.artistID
  
 WHERE (genres.genre = 'Pop');

또한 MySQL에서 다중 테이블 쿼리의 경우 다음 사항에 따라 달라집니다. 쿼리 상황에 따라 어떤 연결 방법이 더 효율적인지 결정하세요.

1. 교차 조인(데카르트 곱) 또는 내부 조인 [INNER | CROSS] JOIN

2. 왼쪽 외부 조인 LEFT [OUTER] JOIN 또는 오른쪽 외부 조인 RIGHT [OUTER] JOIN 참고 지정 연결 조건은 WHERE, ON, USING입니다.

PS: 기본 JOIN 사용법

먼저 2개의 테이블 A와 B가 있다고 가정합니다. 테이블 구조와 필드는 다음과 같습니다.

테이블 A:

ID 이름

1 Tim
2 Jimmy
3 John
4 Tom

테이블 B:

ID Hobby
1 축구
2농구
2테니스
4축구

1． 내부 조인:

Select A.Name, B.Hobby from A, B where A.id = B.id

쿼리 결과는

Name Hobby
Tim Football
Jimmy Basketball
Jimmy Tennis
Tom Soccer

기능은 과 동일합니다.

Select A.Name from A INNER JOIN B ON A.id = B.id

是一样的。这里的INNER JOIN换成CROSS JOIN也是可以的。

2． 外左联结

Select A.Name from A Left JOIN B ON A.id = B.id

典型的外左联结，这样查询得到的结果将会是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空，结果如下：

Name Hobby
Tim Football
Jimmy Basketball，Tennis
John
Tom Soccer

所以从上面结果看出，因为A表中的John记录的ID没有在B表中有对应ID，因此为空，但Name栏仍有John记录。
3． 外右联结
如果把上面查询改成外右联结：

Select A.Name from A Right JOIN B ON A.id = B.id

则结果将会是：

Name Hobby
Tim Football
Jimmy Basketball
Jimmy Tennis
Tom Soccer

这样的结果都是我们可以从外左联结的结果中猜到的了。

说到这里大家是否对联结查询了解多了？这个原本看来高深的概念一下子就理解了，恍然大悟了吧（呵呵，开玩笑了）？最后给大家讲讲MySQL联结查询中的某些参数的作用：
1．USING (column_list)：其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：

a LEFT JOIN b USING (c1,c2,c3)

其作用相当于下面语句

a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

只是用ON来代替会书写比较麻烦而已。

2．NATURAL [LEFT] JOIN：这个句子的作用相当于INNER JOIN，或者是在USING子句中包含了联结的表中所有字段的Left JOIN（左联结）。

3．STRAIGHT_JOIN：由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。

最后要说的就是，在MySQL5.0以后，运算顺序得到了重视，所以对多表的联结查询可能会错误以子联结查询的方式进行。譬如你需要进行多表联结，因此你输入了下面的联结查询：

SELECT t1.id,t2.id,t3.id
FROM t1,t2
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

但是MySQL并不是这样执行的，其后台的真正执行方式是下面的语句：

SELECT t1.id,t2.id,t3.id
FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.id) )
WHERE t1.id=t2.id;

这并不是我们想要的效果，所以我们需要这样输入：

SELECT t1.id,t2.id,t3.id
FROM (t1,t2)
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

MySQL联合查询效率较高，以下例子来说明联合查询（内联、左联、右联、全联）的好处：

T1表结构（用户名,密码）   
userid（int）   usernamevarchar（20）   passwordvarchar（20）   
1   jack  jackpwd   
2   owen  owenpwd   


T2表结构（用户名,密码）   
userid（int）   jifenvarchar（20）   dengjivarchar（20）   
    1   20   3   
    3   50   6   


第一：内联（inner join）
如果想把用户信息、积分、等级都列出来，那么一般会这样写：

select * from T1, T3 where T1.userid = T3.userid
（其实这样的结果等同于select * from T1 inner join T3 on T1.userid=T3.userid ）。

把两个表中都存在userid的行拼成一行（即内联），但后者的效率会比前者高很多，建议用后者（内联）的写法。

SQL语句：
select * from T1 inner join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   

第二：左联（left outer join）
显示左表T1中的所有行，并把右表T2中符合条件加到左表T1中；
右表T2中不符合条件，就不用加入结果表中，并且NULL表示。

SQL语句：
select * from T1 left outer join T2 on T1.userid = T2.userid

运行结果   
T1.userid   username   password   T2.userid   jifen   dengji   
1   jack   jackpwd   1   20   3   
2   owen   owenpwd   NULL   NULL   NULL   

第三：右联（right outer join）。

오른쪽 테이블 T2의 모든 행을 표시하고 왼쪽 테이블 T1의 일치 조건을 오른쪽 테이블 T2에 추가합니다.
왼쪽 테이블 T1의 조건이 충족되지 않으면 추가할 필요가 없습니다. 결과 테이블에 반영되며 NULL이 표시됩니다.

SQL 문:
select * from T1 right Outer Join T2 on T1.userid = T2.userid

실행 결과
T1.userid 사용자 이름 비밀번호 T2.userid jifen dengji
1 jack jackpwd 1 20 3
NULL NULL NULL 3 50 6

넷째: 완전 조인 외부

왼쪽 테이블 T1과 오른쪽 테이블 T2의 모든 행을 표시합니다. 즉, 왼쪽 조인 결과 테이블 + 오른쪽 조인 결과 테이블을 결합한 후 중복된 항목을 필터링합니다.

SQL 문:
select * from T1 완전 외부 조인 T2 on T1.userid = T2.userid

실행 결과
T1.userid 사용자 이름 비밀번호 T2.userid jifen dengji
1 jack jackpwd 1 20 3
2 owen owenpwd NULL NULL NULL
NULL NULL NULL 3 50 6

요약하자면, 공동 쿼리의 경우 효율성이 상대적으로 낮습니다. 높음, 4 이 조합 방법을 유연하게 사용할 수 있다면 기본적으로 복잡한 문장 구조는 더욱 단순해질 것이다.