1

I want to display difference between tables by returning the names in table 1 that are not in Table 2.

Table 1:

+----+-------------+----------+
| id | campaign_id | nickname |
+----+-------------+----------+
| 1  | 55          | name1    |
+----+-------------+----------+
| 2  | 55          | name2    |
+----+-------------+----------+
| 3  | 55          | name3    |
+----+-------------+----------+
| 4  | 40          | name4    |
+----+-------------+----------+
| 5  | 40          | name1    |
+----+-------------+----------+

Table 2:

+----+---------+----------+
| id | book_id | name     |
+----+---------+----------+
| 1  | 55      | name1    |
+----+---------+----------+
| 2  | 55      | name2    |
+----+---------+----------+

Expected result:

+----+-------+
| id | name  |
+----+-------+
| 55 | name3 |
+----+-------+
| 40 | name4 |
+----+-------+
| 40 | name1 |
+----+-------+

Sorry, my 2nd table has name instead of nickname so how I can use USING property?

  • You can edit your question or leave a comment on the provided if clarification is needed. – danblack Feb 21 '19 at 0:26
  • how I can use USING property? It's impossible if field's names differs. – Akina Feb 21 '19 at 5:00
1

Left join where there is no match on the left (IS NULL):

SELECT campaign_id as id, tbl1.nickname as name
FROM tbl1
LEFT JOIN tbl2 ON nickname=name
WHERE tbl2.id IS NULL

Alternately there is a MariaDB-10.3 syntax:

select version();
| version()                                   |
| :------------------------------------------ |
| 10.3.11-MariaDB-1:10.3.11+maria~stretch-log |
create table tbl1 (name varchar(20))
insert into tbl1 values ('n1'),('n2'),('n6')
create table tbl2 (name varchar(20))
insert into tbl2 values ('n1'),('n2'),('n12')
select name from tbl1 except select name from tbl2
| name |
| :--- |
| n6   |

db<>fiddle here

|improve this answer|||||
1
SELECT campaign_id id, nickname name
FROM table1
WHERE NOT EXISTS ( SELECT 1
                   FROM table2
                   WHERE table1.campaign_id = table2.book_id
                     AND table1.nickname = table2.name )

or

SELECT table1.campaign_id id, table1.nickname name
FROM table1
LEFT JOIN table2
       ON table1.campaign_id = table2.book_id
      AND table1.nickname = table2.name
WHERE table2.name IS NULL
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.