Rows that are in table A but not in B-CodePudding

How can I check the rows for columns a, b, c, and d in table A but not in table B?

CodePudding user response：

select a.a,a.b,a.c,a.d
  from tablea as a
except
select b.a,b.b,b.c,b.d
  from tableb as b

CodePudding user response：

If you want to get full rows from table a you can use exists:

select *
from tbl_a
where not exists (
    select *
    from tbl_b
    where tbl_b.a = tbl_a.a
    and   tbl_b.b = tbl_a.b 
    and   tbl_b.c = tbl_a.c 
    and   tbl_b.d = tbl_a.d
)

CodePudding user response：

You can LEFT JOIN the table B to get the common elements between both tables plus the remaining in A (if any field doesn't match the join, B will be NULL), and then remove the inner part by filtering only the NULL values for B

SELECT A.*
FROM A
LEFT JOIN B
    ON A.a = B.a
   AND A.b = B.b
   AND A.c = B.c
WHERE COALESCE(B.a, B.b, B.c) IS NULL;

fiddle

CodePudding user response：

How can I check the rows for columns a, b, c, and d in table A but not in table B?

This will show you the total values for all the columns you indicated in A as well as row data

SELECT
 a.a
,a.b
,a.c
,a.d
,b.e
FROM A as a
LEFT JOIN B as b
ON a.a = a.b
Where   b.a IS NULL 
    and b.b IS NULL  
    and b.c IS NULL
    and b.d IS NULL
GROUP BY GROUPING SETS ((),(a.a,a.b,a.c,a.d))
ORDER BY a.a