Hướng dẫn mysql left join check if exists - mysql bên trái tham gia kiểm tra nếu tồn tại

Question

Tôi có hai bảng có cùng một khóa chính, nhưng một bảng lớn hơn nhiều so với bản kia. Tôi muốn biết ID nào có một hàng trong bảng nhỏ hơn. (Trong ví dụ,

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

4 là lớn và

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

5 là nhỏ). Ngay bây giờ, tôi đang sử dụng tham gia bên ngoài với một trường hợp để xác định xem giá trị B có null hay không. Nó không hoạt động (luôn luôn nhận được 1). Khắc phục điều này sẽ ổn, nhưng phải có một cách tốt hơn. Tôi nên làm như thế nào?

Nội dung chính Show

Tham gia trái / là null
Không phải vào
MySQL, cũng như tất cả các hệ thống khác ngoại trừ SQL Server, có thể tối ưu hóa INSERT INTO dbo.A DEFAULT VALUES; GO 10000 INSERT INTO dbo.B DEFAULT VALUES; GO 10000 8 để trả về INSERT INTO dbo.A DEFAULT VALUES; GO 10000 INSERT INTO dbo.B DEFAULT VALUES; GO 10000 9 vì sớm tìm thấy giá trị phù hợp và đó là hệ thống duy nhất quan tâm để ghi lại hành vi này.
Bản tóm tắt
Trường hợp không tồn tại so với tham gia bên ngoài bên trái?
Làm thế nào để bạn thay thế tồn tại bằng tham gia?
Làm thế nào để bạn sử dụng tồn tại trong tham gia SQL?
Cái nào nhanh hơn không trong hoặc trái tham gia?

SELECT a.id,
       CASE b.id
         WHEN NULL THEN 0
         ELSE 1
         END AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

hỏi ngày 29 tháng 7 năm 2011 lúc 21:08Jul 29, 2011 at 21:08

JMilloyjmilloyjmilloy

7,44610 Huy hiệu vàng 50 Huy hiệu bạc85 Huy hiệu Đồng10 gold badges50 silver badges85 bronze badges

Điều này có cùng logic về những gì bạn đã thể hiện nhưng có mã ngắn hơn:

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

Đã trả lời ngày 29 tháng 7 năm 2011 lúc 21:17Jul 29, 2011 at 21:17

DalendalenDalen

8.7164 Huy hiệu vàng46 Huy hiệu bạc52 Huy hiệu Đồng4 gold badges46 silver badges52 bronze badges

0

Không. Kiểm tra

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

6 trong (các) cột khóa nước ngoài chính xác là cách bạn làm điều này.

Tuy nhiên, không có gì bằng

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

6 (đó không phải là một giá trị), đó là lý do tại sao

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

8 của bạn đi đến phần

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

9. Bạn cần sử dụng

CASE WHEN b.id IS NULL THEN ...

0 để kiểm tra xem một cột là

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

6.

CASE WHEN b.id IS NULL THEN ...

Đã trả lời ngày 29 tháng 7 năm 2011 lúc 21:13Jul 29, 2011 at 21:13

Dan Grossmandan GrossmanDan Grossman

51.2k10 Huy hiệu vàng109 Huy hiệu bạc98 Huy hiệu đồng10 gold badges109 silver badges98 bronze badges

0

Sự khác biệt lớn nhất không phải là tham gia vs không tồn tại, đó là (như đã viết),

CASE WHEN b.id IS NULL THEN ...

2.

Trong ví dụ đầu tiên, bạn nhận được tất cả các cột từ cả

CASE WHEN b.id IS NULL THEN ...

3 và

CASE WHEN b.id IS NULL THEN ...

4, trong khi trong ví dụ thứ hai, bạn chỉ nhận được các cột từ

CASE WHEN b.id IS NULL THEN ...

3.

Trong SQL Server, biến thể thứ hai nhanh hơn một chút trong một ví dụ rất đơn giản:

Tạo hai bảng mẫu:

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

Chèn 10.000 hàng vào mỗi bảng:

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

Xóa mỗi hàng thứ 5 khỏi bảng thứ hai:

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

Thực hiện hai biến thể câu lệnh

CASE WHEN b.id IS NULL THEN ...

6:

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

Kế hoạch thực thi:

Hướng dẫn mysql left join check if exists - mysql bên trái tham gia kiểm tra nếu tồn tại

Biến thể thứ hai không cần thực hiện thao tác bộ lọc vì nó có thể sử dụng toán tử tham gia chống SEMI trái.

Phương pháp nào là tốt nhất để chọn các giá trị có trong một bảng nhưng thiếu trong một phương pháp khác?
This:
SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL
, đây:
SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )
hoặc cái này:
SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )

Cuối cùng, đó là thời gian MySQL.MySQL time.

Như mọi khi, chúng tôi sẽ tạo các bảng mẫu:

Chi tiết tạo bảng

Bảng

CASE WHEN b.id IS NULL THEN ...

7 chứa 100.000 hàng với 10.000 giá trị riêng biệt.100,000 rows with 10,000 distinct values.

Bảng

CASE WHEN b.id IS NULL THEN ...

8 chứa 1.000.000 hàng với 10.000 giá trị riêng biệt.1,000,000 rows with 10,000 distinct values.

Có 10 hàng trong

CASE WHEN b.id IS NULL THEN ...

7 với các giá trị không có trong

CASE WHEN b.id IS NULL THEN ...

8.10 rows in

CASE WHEN b.id IS NULL THEN ...

7 with values not present in

CASE WHEN b.id IS NULL THEN ...

8.

Trong cả hai bảng, trường

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

1 được lập chỉ mục.

Tham gia trái / là null

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

0

Xem kết quả truy vấn và kế hoạch thực hiện

Truy vấn trả về kết quả chính xác trong 0,73 giây.0.73 seconds.

Nếu chúng ta xem xét phần bổ sung của kế hoạch thực hiện, chúng ta sẽ thấy một điều thú vị ở đó:Extra part of the execution plan, we will see an interesting thing there:

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

2

Nó có nghĩa là gì?

Tài liệu MySQL đề cập đến điều này: documentation mentions this:

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

3

MySQL đã có thể thực hiện tối ưu hóa

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

4 trên truy vấn và không kiểm tra nhiều hàng hơn trong bảng này cho kết hợp hàng trước sau khi tìm thấy một hàng phù hợp với tiêu chí
CREATE TABLE dbo.A ( A_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) ); CREATE TABLE dbo.B ( B_ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) ); GO
4. Dưới đây là một ví dụ về loại truy vấn có thể được tối ưu hóa theo cách này: was able to do a

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

4 optimization on the query and does not examine more rows in this table for the previous row combination after it finds one row that matches the

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

4 criteria. Here is an example of the type of query that can be optimized this way:

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

1

Giả sử rằng

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

6 được định nghĩa là

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

7. Trong trường hợp này, MySQL quét

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

8 và tìm kiếm các hàng trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9 bằng cách sử dụng các giá trị của

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

0. Nếu MySQL tìm thấy một hàng phù hợp trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9, nó sẽ biết rằng

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

6 không bao giờ có thể là

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

6 và không quét qua các hàng còn lại trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9 có cùng giá trị ID. Nói cách khác, với mỗi hàng trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

8, MySQL chỉ cần thực hiện một lần tra cứu duy nhất trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9, bất kể có bao nhiêu hàng thực sự khớp trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9.MySQL scans

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

8 and looks up the rows in

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9 using the values of

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

0. If MySQL finds a matching row in

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9, it knows that

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

6 can never be

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

6, and does not scan through the rest of the rows in

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9 that have the same id value. In other words, for each row in

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

8, MySQL needs to do only a single lookup in

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9, regardless of how many rows actually match in

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9.

Đây chính xác là trường hợp của chúng tôi.

MySQL, cũng như tất cả các hệ thống khác ngoại trừ SQL Server, có thể tối ưu hóa

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

8 để trả về
INSERT INTO dbo.A DEFAULT VALUES; GO 10000 INSERT INTO dbo.B DEFAULT VALUES; GO 10000
9 vì sớm tìm thấy giá trị phù hợp và đó là hệ thống duy nhất quan tâm để ghi lại hành vi này., as well as all other systems except SQL Server, is able to optimize

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

8 to return

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

9 as soon the matching value is found, and it is the only system that cared to document this behavior.

Tuy nhiên, giả định rằng

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

6 nên được định nghĩa là

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

7, tuy nhiên, quá mạnh, vì thành công

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

2 về điều kiện bình đẳng ngụ ý rằng giá trị được tìm thấy là ____37.

Vì MySQL không có khả năng sử dụng các thuật toán tham gia

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

4 và

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

5, nên

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

6 duy nhất có khả năng là

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

7, đó chính xác là những gì chúng ta thấy trong kế hoạch truy vấn (mặc dù thực tế là MySQL không gọi nó là). Tuy nhiên, hành vi này là những gì một

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

6 thực hiện: nó kiểm tra các giá trị từ bảng bên trái chỉ so với một trong mỗi giá trị riêng biệt trong bảng bên phải, bỏ qua các bản sao.MySQL is not capable of using

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

4 and

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

5 join algorithms, the only

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

6 it is capable of is the

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

7, which is exactly what we see in the query plan (despite the fact that MySQL doesn't call it that). However, this behavior is what an

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

6 does: it checks the values from the left table against only one of each distinct values in the right table, skipping the duplicates.

Không phải vào

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

2

Xem kết quả truy vấn và kế hoạch thực hiện

Truy vấn trả về kết quả chính xác trong 0,73 giây.

Nếu chúng ta xem xét phần bổ sung của kế hoạch thực hiện, chúng ta sẽ thấy một điều thú vị ở đó:

Nó có nghĩa là gì?

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

4

Tài liệu MySQL đề cập đến điều này:

MySQL đã có thể thực hiện tối ưu hóa

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

4 trên truy vấn và không kiểm tra nhiều hàng hơn trong bảng này cho kết hợp hàng trước sau khi tìm thấy một hàng phù hợp với tiêu chí

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

4. Dưới đây là một ví dụ về loại truy vấn có thể được tối ưu hóa theo cách này:MySQL applies

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

6 optimization to the subquery: it uses the index scan over

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

7 and returns as soon as it finds (or not finds) a row.

Giả sử rằng

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

6 được định nghĩa là

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

7. Trong trường hợp này, MySQL quét

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

8 và tìm kiếm các hàng trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9 bằng cách sử dụng các giá trị của

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

0. Nếu MySQL tìm thấy một hàng phù hợp trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9, nó sẽ biết rằng

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

6 không bao giờ có thể là

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

6 và không quét qua các hàng còn lại trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9 có cùng giá trị ID. Nói cách khác, với mỗi hàng trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

8, MySQL chỉ cần thực hiện một lần tra cứu duy nhất trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9, bất kể có bao nhiêu hàng thực sự khớp trong

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

9.MySQL takes this into account.

Đây chính xác là trường hợp của chúng tôi.

MySQL, cũng như tất cả các hệ thống khác ngoại trừ SQL Server, có thể tối ưu hóa INSERT INTO dbo.A DEFAULT VALUES; GO 10000 INSERT INTO dbo.B DEFAULT VALUES; GO 10000 8 để trả về INSERT INTO dbo.A DEFAULT VALUES; GO 10000 INSERT INTO dbo.B DEFAULT VALUES; GO 10000 9 vì sớm tìm thấy giá trị phù hợp và đó là hệ thống duy nhất quan tâm để ghi lại hành vi này.

SELECT a.id,NOT ISNULL(b.id) AS exists
FROM a LEFT OUTER JOIN b
  ON a.id=b.id;

3

Xem kết quả truy vấn và kế hoạch thực hiện

Truy vấn trả về kết quả chính xác trong 0,73 giây.

Nếu chúng ta xem xét phần bổ sung của kế hoạch thực hiện, chúng ta sẽ thấy một điều thú vị ở đó:MySQL is the only system that produces three different plans for three different methods.

Kế hoạch không khác nhau nhiều: MySQL biết tra cứu chỉ mục là gì và

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

6 là gì và nó kết hợp chúng với nhau.MySQL does know what an index lookup is and what

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

6 is and it does combine them together.

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

6 trong MySQL được tối ưu hóa để nó trả về ngay khi tìm thấy giá trị đầu tiên. Vì vậy, truy vấn này trong thực tế là một

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

6 cũng như hai truy vấn đầu tiên.MySQL is optimized so that it returns as soon as the first value is found. So this query in fact is an

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

6 as well as first two queries are.

Truy vấn này, tuy nhiên, kém hiệu quả hơn một chút so với hai lần trước: phải mất 0,92 giây.0.92 s.

Đây không phải là giảm nhiều hiệu suất, tuy nhiên, truy vấn mất thêm 27% thời gian.27% more time.

Thật khó để nói lý do chính xác cho điều này, vì sự sụt giảm này là tuyến tính và dường như không phụ thuộc vào phân phối dữ liệu, số lượng giá trị trong cả hai bảng, v.v., miễn là cả hai trường được lập chỉ mục. Vì có ba đoạn mã trong MySQL mà Essentialy thực hiện một công việc, nên có thể mã chịu trách nhiệm cho

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

6 làm cho một số kiểm tra thêm cần thêm thời gian.MySQL that essentialy do one job, it is possible that the code responsible for

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

6 makes some kind of an extra check which takes extra time.

Bản tóm tắt

MySQL có thể tối ưu hóa cả ba phương pháp để thực hiện một loại

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

7. can optimize all three methods to do a sort of

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

7.

Nó sẽ lấy từng giá trị từ

CASE WHEN b.id IS NULL THEN ...

7 và tìm nó trong chỉ mục trên

SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

1. Trong trường hợp một chỉ số bị truy cập hoặc bỏ lỡ chỉ mục, vị từ tương ứng sẽ ngay lập tức trả về

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

9 hoặc

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )

4, và quyết định trả lại hàng từ

CASE WHEN b.id IS NULL THEN ...

7 hoặc không được thực hiện ngay lập tức mà không kiểm tra các hàng khác trong

CASE WHEN b.id IS NULL THEN ...

8.

Tuy nhiên, ba phương pháp này tạo ra ba kế hoạch khác nhau được thực hiện bởi ba đoạn mã khác nhau. Mã thực thi

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

6 vị ngữ kém hiệu quả hơn khoảng 30% so với mã thực thi

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )

8 và

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

4 được tối ưu hóa để sử dụng phương thức

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

3.30% less efficient than those that execute

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )

8 and

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

4 optimized to use

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

3 method.

Đó là lý do tại sao cách tốt nhất để tìm kiếm các giá trị bị thiếu trong MySQL là sử dụng

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

8 hoặc

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

8 thay vì

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )

3.MySQL is using a

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

8 or

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

8 rather than

SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )

3.

Trường hợp không tồn tại so với tham gia bên ngoài bên trái?

Tồn tại và không tồn tại cả ngắn mạch - ngay khi bản ghi khớp với các tiêu chí, nó được bao gồm hoặc lọc ra và trình tối ưu hóa chuyển sang bản ghi tiếp theo. Tham gia còn lại sẽ tham gia tất cả các hồ sơ bất kể chúng có khớp hay không, sau đó lọc tất cả các bản ghi không phù hợp.LEFT JOIN will join ALL RECORDS regardless of whether they match or not, then filter out all non-matching records.

Làm thế nào để bạn thay thế tồn tại bằng tham gia?

Làm thế nào để thay thế nơi tồn tại với tham gia bên trong?..

Chọn PR. Partid ..

các bộ phận. NOP_PART PR với (Nolock).

bên trong tham gia ngoại khóa. DBO. ....

Các bộ phận tham gia bên trong. NOP_PackageAttribution PA với (Nolock) trên PA. ....

Bên trong tham gia DBO. Core_datadefinitiondetails DD với (nolock) trên pa. [Key] = dd. ....

where..

tồn tại (chọn 1 từ extractreport. DBO ..

Làm thế nào để bạn sử dụng tồn tại trong tham gia SQL?

SQL tồn tại nhà điều hành..

Chọn cột_name (S) từ Table_Name.Nơi tồn tại.(Chọn Cột_Name từ Table_Name nơi điều kiện) ;.

Thí dụ.Chọn Nhà cung cấp.Từ các nhà cung cấp.....

Thí dụ.Chọn Nhà cung cấp.Từ các nhà cung cấp ..

Cái nào nhanh hơn không trong hoặc trái tham gia?

Truy vấn tham gia bên trái chậm hơn so với truy vấn tham gia bên trong vì nó đang làm nhiều việc hơn.LEFT JOIN query is slower than the INNER JOIN query because it's doing more work.

programming mysql LEFT JOIN alternative

Hướng dẫn mysql left join check if exists - mysql bên trái tham gia kiểm tra nếu tồn tại

Tham gia trái / là null

Không phải vào

Bản tóm tắt

Trường hợp không tồn tại so với tham gia bên ngoài bên trái?

Làm thế nào để bạn thay thế tồn tại bằng tham gia?

Làm thế nào để bạn sử dụng tồn tại trong tham gia SQL?

Cái nào nhanh hơn không trong hoặc trái tham gia?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội