As you can see is in the above diagram, the MINUS operator will return only those rows which are present in the result set from Table1 and not present in the result set of Table2. Basic Syntax: SELECT column1 , column2 , ...
There are basically 4 techniques for this task, all of them standard SQL.
NOT EXISTS
Often fastest in Postgres.
SELECT ip
FROM login_log l
WHERE NOT EXISTS (
SELECT -- SELECT list mostly irrelevant; can just be empty in Postgres
FROM ip_location
WHERE ip = l.ip
);
Also consider:
LEFT JOIN / IS NULL
Sometimes this is fastest. Often shortest. Often results in the same query plan as NOT EXISTS
.
SELECT l.ip
FROM login_log l
LEFT JOIN ip_location i USING (ip) -- short for: ON i.ip = l.ip
WHERE i.ip IS NULL;
EXCEPT
Short. Not as easily integrated in more complex queries.
SELECT ip
FROM login_log
EXCEPT ALL -- "ALL" keeps duplicates and makes it faster
SELECT ip
FROM ip_location;
Note that (per documentation):
duplicates are eliminated unless
EXCEPT ALL
is used.
Typically, you'll want the ALL
keyword. If you don't care, still use it because it makes the query faster.
NOT IN
Only good without NULL
values or if you know to handle NULL
properly. I would not use it for this purpose. Also, performance can deteriorate with bigger tables.
SELECT ip
FROM login_log
WHERE ip NOT IN (
SELECT DISTINCT ip -- DISTINCT is optional
FROM ip_location
);
NOT IN
carries a "trap" for NULL
values on either side:
Similar question on dba.SE targeted at MySQL:
A.) The command is NOT EXISTS, you're missing the 'S'.
B.) Use NOT IN instead
SELECT ip
FROM login_log
WHERE ip NOT IN (
SELECT ip
FROM ip_location
)
;
SELECT *
FROM testcases1 t
WHERE NOT EXISTS (
SELECT 1
FROM executions1 i
WHERE t.tc_id = i.tc_id and t.pro_id=i.pro_id and pro_id=7 and version_id=5
) and pro_id=7 ;
Here testcases1 table contains all datas and executions1 table contains some data among testcases1 table. I am retrieving only the datas which are not present in exections1 table. ( and even I am giving some conditions inside that you can also give.) specify condition which should not be there in retrieving data should be inside brackets.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With