Select * from Table1 That Does Not Exist in Table2 with Conditional

Select * from table1 that does not exist in table2 with conditional

Using LEFT JOIN/IS NULL:

   SELECT t.*
     FROM TABLE_LIST t
LEFT JOIN TABLE_LOG tl ON tl.jid = t.jid
    WHERE tl.jid IS NULL

Using NOT IN:

SELECT t.*
  FROM TABLE_LIST t
 WHERE t.jid NOT IN (SELECT tl.jid
                       FROM TABLE_LOG tl
                   GROUP BY tl.jid)

Using NOT EXISTS:

SELECT t.*
  FROM TABLE_LIST t
 WHERE NOT EXISTS(SELECT NULL
                    FROM TABLE_LOG tl
                   WHERE tl.jid = t.jid)

FYI
LEFT JOIN/IS NULL and NOT IN are equivalent in MySQL - they will perform the same, while NOT EXISTS is slower/less efficient. For more details: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

SQL select from Table1 that has no value in Table2

You can use a left join and select only the records having null in the right table in some column which shouldn't be null if there was a corresponding record.

SELECT a.NUMBER1, a.NUMBER2, a.TYPE
FROM
    Table_1 a
    LEFT JOIN Table_2 b
        ON a.NUMBER1 = b.NUMBER1 AND
           a.NUMBER2 = b.NUMBER2
WHERE
    b.NUMBER1 IS NULL

select particular data from table1 which is not in table2 with where clause

You can just change the != to not in:

select name, id
from table1
where id not in (select table1_id
                 from table2 join
                      table3
                      on table2.acc_id = table3.acc_id
                      where table2.did = 4759505 and table2.acc_id = 2
                ) and
      table1.acc_id = 2;

Note: you should also be sure that table1_id in the subquery is never NULL. NOT IN can be non-intuitive in this case. Often, I prefer NOT EXISTS:

select name, id
from table1
where not exists (select table1_id
                  from table2 join
                       table3
                       on table2.acc_id = table3.acc_id
                       where table2.did = 4759505 and table2.acc_id = 2 and
                             table1_id = table1.id
                 ) and
      table1.acc_id = 2;

This handles the NULL value more intuitively.

Oracle SQL - Finding records from table1 where table2 condition does not exist

You've almost got the right answer.
Try this query:

SELECT t1.wonum
FROM table1 t1
WHERE t1.wonum NOT IN (
  SELECT t2.wonum
  FROM table2 t2
  WHERE t2.wonum = t1.wonum
  AND t2.holdstatus = 'COMP'
);

This should give you all of the records you need. In this case, just record 123.

You can also do it using a NOT EXISTS query. Generally, they perform better, but if you have a small table, then it wouldn't make that much of a difference.

SELECT t1.wonum
FROM table1 t1
WHERE NOT EXISTS (
  SELECT t2.wonum
  FROM table2 t2
  WHERE t2.wonum = t1.wonum
  AND t2.holdstatus = 'COMP'
);

insert into table2 select from table1 if the whole row not exists in table2

Within the not exist statement you need to have a where condition, your query is missing that,

         INSERT INTO   [server2].[db2].[dbo].[pcodes]  (parcode,pname,unit,typ) 
              SELECT parcode,pname,unit,typ 
              FROM [server1].[db1].[dbo].[pcodes] a 
              WHERE NOT EXISTS 
              (SELECT parcode,pname,unit,typ from  [server2].[db2].[dbo].[pcodes] b
              WHERE a.parcode=b.parcode and a.pname=b.pname and a.unit=b.unit and a.typ=b.typ )  

Find records from table 1 that are not in table 2

You can do it different ways. The LEFT JOIN way that you have selected needs to be written like this:

SELECT tblEmployee.EmpID, a.CatelogID
FROM tblEmployee LEFT JOIN 
(SELECT * FROM qryAllCompletedCourses WHERE qryAllCompletedCourses.CatelogID=8) a
ON tblEmployee.EmpID = a.EmpID
WHERE a.EmpID IS NULL;

Please note that the above query is "Access specific", as Access does not allow any conditions in the ON clause that are not the direct comparison between the columns of the joined tables. With any other RDBMS you would use this query:

SELECT tblEmployee.EmpID, qryAllCompletedCourses.CatelogID
FROM tblEmployee LEFT JOIN 
qryAllCompletedCourses 
ON tblEmployee.EmpID = qryAllCompletedCourses.EmpID and qryAllCompletedCourses.CatelogID=8
WHERE a.EmpID IS NULL;

The other way of doing it is to use the NOT IN:

SELECT tblEmployee.EmpID
FROM tblEmployee 
WHERE tblEmployee.EmpID NOT IN (SELECT EmpID FROM qryAllCompletedCourses 
     WHERE qryAllCompletedCourses.CatelogID=8);

And to complete it all here is a way of doing it with NOT EXISTS:

SELECT tblEmployee.EmpID
FROM tblEmployee 
WHERE NOT EXISTS(SELECT 1 FROM qryAllCompletedCourses 
    WHERE qryAllCompletedCourses.CatelogID=8 and 
    tblEmployee.EmpID = qryAllCompletedCourses.EmpID);

---

Related Topics