T-Sql: Comparing Two Tables - Records That Don't Exist in Second Table

T-SQL: Comparing Two Tables - Records that don't exist in second table

Instead of using UNION, use EXCEPT, ( or INTERSECT to get only records in both )
as described in

msdn EXCEPT Link for Sql2k8

msdn EXCEPT Link for Sql2k5

Compare two tables and show the value of another table if exist, if not exist just show status in SQL

Just use a LEFT JOIN, and COALESCE any NULL values to Data not Exist:

SELECT a.id, COALESCE(b.value, 'Data not exist') AS value
FROM a
LEFT JOIN b ON b.id = a.id

Output:

id  value
1   10
2   9
3   7
4   8
5   Data not exist
6   Data not exist
7   Data not exist
8   Data not exist
9   Data not exist
10  Data not exist

Demo on dbfiddle

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 '*')

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

Select Rows which don't exist in second table based on multiple columns

Assuming none of the key values are NULL, you can do it this way. One NULL comparison is suficient:

select ta.a, ta.b, ta.c
from TableA ta left join
     TableB tb
     on ta.a = tb.a and ta.b = tb.b and - ta.c = tb.c 
where tb.a is null;

I don't recommend using a subquery to define d. MySQL tends to materialize subqueries, which adds additional overhead and can prevent indexes from being used.

Compare two tables and give the output record which does not exist in 1st table

select T2.Name
from Table2 as T2
where not exists (select * from Table1 as T1 where T1.Name = T2.Name)

See this article about performance of different implementations of anti-join (for SQL Server).

How can I Compare two tables without relationship (1 table with 40k records and the other one with 7k records)?

You don't make it easy, but here is a working solution. In the future try to use this type of code in your question so we can focus on the query and solution. I have edited some of your dates to make the example work.

EDIT: New Code

declare @cal table (
    calID int not null
,   date_ date not null
,   isWeekday bit not null
,   isHoliday bit not null
,   year_  int not null
);

insert into @cal (calID, date_, isWeekday, isHoliday, year_)
select 1,    '1-Jan-2010',  1,  1,  2010 union all
select 2,    '2-Jan-2010',  0,  0,  2010 union all
select 3,    '3-Jan-2010',  0,  0,  2010 union all
select 4,    '4-Jan-2010',  1,  0,  2010 union all
select 5,    '5-Jan-2010',  1,  0,  2010 union all
select 6,    '6-Jan-2010',  1,  0,  2010 union all
select 7,    '7-Jan-2010',  1,  0,  2010 union all
select 8,    '8-Jan-2010',  1,  0,  2010 union all
select 9,    '9-Jan-2010',  0,  0,  2010 union all
select 10,  '10-Jan-2010',  0,  0,  2010 union all
select 11,  '11-Jan-2010',  1,  0,  2010 union all
select 12,  '12-Jan-2010',  1,  0,  2010 union all
select 13,  '13-Jan-2010',  1,  0,  2010 union all
select 14,  '14-Jan-2010',  1,  0,  2010 union all
select 15,  '15-Jan-2010',  1,  0,  2010 union all
select 16,  '16-Jan-2010',  0,  0,  2010 union all
select 17,  '17-Jan-2010',  0,  0,  2010 union all
select 18,  '18-Jan-2010',  1,  1,  2010 union all
select 19,  '19-Jan-2010',  1,  0,  2010 union all
select 20,  '20-Jan-2010',  1,  0,  2010 union all
select 21,  '21-Jan-2010',  1,  0,  2010 union all
select 22,  '22-Jan-2010',  1,  0,  2010 union all
select 23,  '23-Jan-2010',  0,  0,  2010 union all
select 24,  '24-Jan-2010',  0,  0,  2010 union all
select 25,  '25-Jan-2010',  1,  0,  2010 union all
select 26,  '26-Jan-2010',  1,  0,  2010;

declare @date table(
    dateID int identity(1,1) not null
,   date2 date null
,   date3 date null
,   date4 date null
,   date5 date null
);

insert into @date (date2, date3, date4, date5)
select '6/20/2009', NULL,   NULL,   '7/19/2009'   union all
select '1/2/2010',  NULL,   NULL,   '1/19/2010'   union all
select '1/4/2010',  NULL,   NULL,   '1/15/2010'  union all
select '1/2/2010',  NULL,   NULL,   '1/22/2010'  union all
select '9/17/2009', NULL,   NULL,   '10/26/2009'  union all
select '6/4/2009',  NULL,   NULL,   '6/24/2009';

;with cte as (
        select dateid
             , b.date_
          from @date
         cross apply ( 
                        Select Top (DateDiff(DAY,date2,IsNull(date5,date2))+1) DateAdd(DAY, -1+Row_Number() Over (Order By 1/0),date2) date_
                          from master..spt_values n1
                        ) b
            )

select distinct b.dateID
     , c.date2
     , c.date5
     , count(*) over(order by b.dateid) cnt
  from @cal a
  join cte b
    on a.date_ = b.date_
  join @date c
    on b.dateid = c.dateid
 where isWeekday = 1
   and isHoliday = 0

you could change out the from master..spt_values n1

for something like this:

 ;with E00(n) as (select 1 union all select 1)
     , E02(n) as (select 1 from E00 a, E00 b)
     , E04(n) as (select 1 from E02 a, E02 b)
     , E08(n) as (select 1 from E04 a, E04 b)
     , E16(n) as (select 1 from E08 a, E08 b)
     , E32(n) as (select 1 from E16 a, E16 b)
, cteTally(d) as (select row_number() over (order by n) from E32)

        , cte as (
       select dateid
            , b.date_
         from @date
  cross apply   ( 
                        select top (datediff(day,date2,isnull(date5,date2))+1) dateadd(day, -1+row_number() over(order by 1/0),date2) date_
                          from cteTally
                        ) b
                )

       select distinct b.dateID
            , c.date2
            , c.date5
            , count(*) over(order by b.dateid) cnt
         from @cal a
         join cte b
           on a.date_ = b.date_
         join @date c
           on b.dateid = c.dateid
        where isWeekday = 1
          and isHoliday = 0

How to compare two tables and select not exist records from first table in SQL

Your current EXCEPT statement is correct as you need to check records which are not exists in second table from first table.

select t1.* from Upload$ t1
LEFT OUTER JOIN Sheet1$ S 
       ON S.MRN = t1.MRN AND t1.FileName = S.FileName 
WHERE S.MRN IS NULL AND S.FileName IS NULL

So, here is alternative approach via JOINs (i.e. LEFT OUTER JOIN) to check record which are not exists in second table from first table and return not exists records from first table

---

Related Topics