Select Rows Not in Another Table, SQL Server Query

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

How to select rows with no matching entry in another table?

Here's a simple query:

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The key points are:

1. LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2.

2. The WHERE t2.ID IS NULL clause; this will restrict the results returned to only those rows where the ID returned from Table2 is null - in other words there is NO record in Table2 for that particular ID from Table1. Table2.ID will be returned as NULL for all records from Table1 where the ID is not matched in Table2.

Select rows not in another table, SQL Server query

SELECT SubjectID, DepartmentID, SubjectName, SubjectDescription, SubjectShortCode
FROM BS_Subject 
WHERE NOT EXISTS 
(SELECT SubjectToClassID FROM BS_SubjectToClass WHERE 
BS_Subject.SubjectID = BS_SubjectToClass.SubjectID
AND BS_SubjectToClass.ClassID =2)

SQL - SELECT rows NOT EXISTS in another table

 SELECT t1.titleid
 FROM tablea t1
 LEFT JOIN tableb t2 ON t2.title + '_00' = t1.titleid
 WHERE t2.title IS NULL

You want to pull Data from Table A , do a left join on Table B and pull data where TableB.Title is null.

Your Query was trying to pull data where TableA.Title is NULL.

How to select data from the table not exist in another table sql

I think your table table1 and table2 have index on their name column, so you can try this:

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

May be id column existed, if not, use t2.name as a replacement for t2.id

SQL to find record with a value in one table but not another table

Assuming your IDs do not change in either table so that ID 100 in Address always points to ID 100 in Address_Backup:

  SELECT * 
    FROM Address_Backup
     WHERE ID NOT IN (SELECT ID FROM Address) AND Code = 'OTHER'

using NOT EXISTS

 SELECT * 
  FROM Address_Backup AS B
  WHERE NOT EXISTS (SELECT 1 FROM Address WHERE ID = B.ID) AND Code = 'OTHER'

Find records from one table which don't exist in another

There's several different ways of doing this, with varying efficiency, depending on how good your query optimiser is, and the relative size of your two tables:

This is the shortest statement, and may be quickest if your phone book is very short:

SELECT  *
FROM    Call
WHERE   phone_number NOT IN (SELECT phone_number FROM Phone_book)

alternatively (thanks to Alterlife)

SELECT *
FROM   Call
WHERE  NOT EXISTS
  (SELECT *
   FROM   Phone_book
   WHERE  Phone_book.phone_number = Call.phone_number)

or (thanks to WOPR)

SELECT * 
FROM   Call
LEFT OUTER JOIN Phone_Book
  ON (Call.phone_number = Phone_book.phone_number)
  WHERE Phone_book.phone_number IS NULL

(ignoring that, as others have said, it's normally best to select just the columns you want, not '*')

Select rows that do not contain a word from another table

contains means Oracle Text; cross join means Cartesian product (usually performance nightmare).

One option which avoids both of these is instr function (which checks existence of the constraint_word in text, but this time using inner join) and the minus set operator.

Something like this, using sample data you posted:

SQL> select * from text_table;

TEXT
---------------------------
word1.apple; word3, example
word1, apple, word2.car
word1 word2 orange word3
mushroomword1 word2 word3
word1 car
qwerty

6 rows selected.

SQL> select * From words_table;

CONSTRAI
--------
example
apple
orange
mushroom
car
qwerty

6 rows selected.

SQL>

---

As you said, initially query shouldn't return anything because all constraint_words exist in text:

SQL> select c.text
  2  from text_table c
  3  minus
  4  select b.text
  5  from words_table a join text_table b on instr(b.text, a.constraint_word) > 0;

no rows selected

Let's modify one of text rows:

SQL> update text_table set text = 'xxx' where text = 'qwerty';

1 row updated.

What's the result now?

SQL> select c.text
  2  from text_table c
  3  minus
  4  select b.text
  5  from words_table a join text_table b on instr(b.text, a.constraint_word) > 0;

TEXT
---------------------------
xxx

SQL>

Right; text we've just modified.

---

Related Topics