Pertanyaan menghapus baris dari SELECT berdasarkan kolom dalam tabel yang berbeda


Saya cukup banyak mencari cara untuk menyaring baris dari SELECT satu tabel berdasarkan nilai-nilai tertentu dalam baris tabel lain.

Saya bereksperimen dengan struktur contoh di bawah ini. Saya punya tabel konten posting blog (satu baris per posting blog), dan tabel lain metadata tentang posting (satu baris per pasangan nilai-kunci; setiap baris dengan kolom yang mengaitkannya dengan posting blog; banyak baris per posting blog). Saya ingin menarik deretan posts hanya jika tidak ada baris dalam metadata dimana metadata.pid=posts.pid AND metadata.k='optout'. Artinya, untuk contoh struktur di bawah ini, saya hanya ingin mendapatkan kembali posts.id=1 baris.

(Berdasarkan apa yang sudah saya coba) JOINs tidak berakhir menghapus posting yang memiliki beberapa metadata di mana metadata.k='optout', karena baris lain dari metadata untuk itu pid berarti itu membuatnya menjadi hasil.

mysql> select * from posts;
+-----+-------+--------------+
| pid | title | content      |
+-----+-------+--------------+
|   1 | Foo   | Some content |
|   2 | Bar   | More content |
|   3 | Baz   | Something    |
+-----+-------+--------------+
3 rows in set (0.00 sec)

mysql> select * from metadata;
+------+-----+--------+-----------+
| mdid | pid | k      | v         |
+------+-----+--------+-----------+
|    1 |   1 | date   | yesterday |
|    2 |   1 | thumb  | img.jpg   |
|    3 |   2 | date   | today     |
|    4 |   2 | optout | true      |
|    5 |   3 | date   | tomorrow  |
|    6 |   3 | optout | true      |
+------+-----+--------+-----------+
6 rows in set (0.00 sec)

Subkueri dapat memberi saya kebalikan dari apa yang saya inginkan:

mysql> select posts.* from posts where pid = any (select pid from metadata where k = 'optout');
+-----+-------+--------------+
| pid | title | content      |
+-----+-------+--------------+
|   2 | Bar   | More content |
|   3 | Baz   | Something    |
+-----+-------+--------------+
2 rows in set (0.00 sec)

... tetapi menggunakan pid != any (...) memberi saya semua 3 baris dalam posting, menyebabkan setiap tunggal pid memiliki baris metadata di mana k!='optout'.


5
2018-01-12 06:59


asal


Jawaban:


Terdengar seperti Anda ingin melakukan LEFT JOIN dan kemudian memeriksa hasil di mana nilai tabel yang tergabung adalah NULL, menunjukkan bahwa tidak ada catatan yang bergabung seperti itu.

Sebagai contoh:

SELECT * FROM posts 
LEFT JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = 'optout')
WHERE metadata.mdid IS NULL;

Ini akan memilih baris apa pun dari tabel posts yang tidak ada yang sesuai metadata baris ada dengan nilai k = 'optout'.

edit: Perlu dicatat bahwa ini adalah properti kunci dari gabungan kiri dan tidak akan berfungsi dengan penggabungan reguler; gabungan kiri akan selalu menghasilkan nilai dari tabel pertama, meskipun tidak ada nilai yang cocok dalam tabel gabungan, memungkinkan Anda untuk melakukan pemilihan berdasarkan tidak adanya baris tersebut.

sunting 2: Mari kita perjelas apa yang terjadi di sini dengan menghormati LEFT JOIN versus JOIN (yang saya sebut sebagai INNER JOIN untuk kejelasan tetapi dapat ditukarkan di MySQL).

Misalkan Anda menjalankan salah satu dari dua kueri ini:

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
FROM posts 
INNER JOIN metadata ON posts.pid = metadata.pid;

atau

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
FROM posts 
LEFT JOIN metadata ON posts.pid = metadata.pid;

Kedua kueri menghasilkan set hasil berikut:

+-----+-------+--------------+------+-------+-----------+
| pid | title | content      | mdid | k     | v         |
+-----+-------+--------------+------+-------+-----------+
|   1 | Foo   | Some content |    1 | date  | yesterday |
|   1 | Foo   | Some content |    2 | thumb | img.jpg   |
+-----+-------+--------------+------+-------+-----------+

Sekarang, anggaplah kita memodifikasi kueri untuk menambahkan kriteria tambahan untuk "optout" yang disebutkan. Pertama, itu INNER JOIN:

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
FROM posts 
INNER JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = "optout");

Seperti yang diharapkan, ini tidak memberikan hasil:

Empty set (0.00 sec)

Sekarang, mengubahnya menjadi a LEFT JOIN:

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
FROM posts 
LEFT JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = "optout");

Ini TIDAK menghasilkan satu set hasil:

+-----+-------+--------------+------+------+------+
| pid | title | content      | mdid | k    | v    |
+-----+-------+--------------+------+------+------+
|   1 | Foo   | Some content | NULL | NULL | NULL |
+-----+-------+--------------+------+------+------+

Perbedaan antara satu INNER JOIN dan a LEFT JOIN apakah itu sebuah INNER JOIN hanya akan mengembalikan hasil jika baris dari BOTH bergabung dengan tabel yang cocok. Di sebuah LEFT JOIN, mencocokkan baris dari tabel pertama akan SELALU dikembalikan, terlepas dari apakah ada yang ditemukan untuk bergabung. Dalam banyak kasus tidak masalah yang mana yang Anda gunakan, tetapi penting untuk memilih yang benar agar tidak mendapatkan hasil yang tidak diharapkan.

Jadi dalam hal ini, kueri yang disarankan:

SELECT posts.*, metadata.mdid, metadata.k, metadata.v 
LEFT JOIN metadata ON (posts.pid = metadata.pid AND metadata.k = 'optout')
WHERE metadata.mdid IS NULL;

Akan mengembalikan hasil yang sama seperti di atas:

+-----+-------+--------------+------+------+------+
| pid | title | content      | mdid | k    | v    |
+-----+-------+--------------+------+------+------+
|   1 | Foo   | Some content | NULL | NULL | NULL |
+-----+-------+--------------+------+------+------+

Semoga itu membersihkannya! Bergabung adalah hal yang bagus untuk dipelajari, memiliki pemahaman penuh tentang kapan menggunakan yang mana yang sangat bagus.


8
2018-01-12 07:03



Anda dapat mencoba sesuatu seperti

select  p.* 
from    posts p
where   NOT EXISTS (
                        select  pid 
                        from    metadata 
                        where   k = 'optout' 
                        and     pid = p.pid
                    )

3
2018-01-12 07:04