[FAQ Very basic] Are these two SQL queries same.

Question

I have tested this and looks to me that they are same... but just want to confirm 

CREATE TYPE UserNameListType as TAble
(
	Kerberos varchar(50)
);
GO

declare @UserList as UserNameListType;
insert into @UserList values ('A'), ('B'), ('C'), ('D')

-- query 1
select ul.Kerberos from @UserList ul
left outer join TableA a on a.Kerberos = ul.Kerberos
left outer join TableB b on b.Kerberos = ul.Kerberos
where a.Kerberos is null and b.Kerberos is null

-- query 2
select Kerberos from @UserList ul 
except 
select Kerberos from TableA where 
except
select Kerberos from TableB

---

val it: unit=()

Answer 1

if you use VS2010, look at Data -> Data Compare -> New Data Comparison...

I use this to validate calculations between two methods are within a certain margin of error:

DECLARE @t TABLE(PK VARCHAR(10) PRIMARY KEY CLUSTERED, Kerb varchar(50))

SELECT
o.[Value],
n.[Value]
FROM (
SELECT PK, Kerb [Value]
FROM @t
) o
FULL OUTER JOIN (
SELECT PK, Kerb [Value]
FROM @t
) n
ON n.[PK] = o.[PK]
WHERE NOT(
(CASE WHEN
CASE WHEN o.[Value] <> 0.0 THEN ABS(o.[Value] - n.[Value]) / o.[Value] ELSE 0.0 END < 0.01 THEN 1 ELSE 0 END = 1 -- Relative Error
OR ABS(o.[Value] - n.[Value]) < 0.01 -- Absolute Error
OR CASE WHEN n.[Value] IS NULL AND o.[Value] IS NULL THEN 1 ELSE 0 END = 1) -- Both are NULL
OR n.[Value] = o.[Value] -- Values are equal
);

Answer 2

Both queries look the same to me.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

yes its same

please  marks as answer if  correct

Answer 4

Nope... one has a syntax error... (the spurious "where").

select Kerberos from @UserList ul 
except 
select Kerberos from TableA where   /* Pretty safe to say this was a typo?  */
except
select Kerberos from TableB

Apart from that, it's in effect accomplishing the same thing.  My personal preference is to be more specific via parentheses, though, so although in this case it's the same result, this looks more readable (my opinion, of course):

Select Kerberos from UL
EXCEPT
(
	Select Kerberos from TableA
	UNION 
	SELECT Kerberos FROM TABLEB
)

Answer 5

No - they are not the same.  Except will eliminate duplicates and NULLs.  Joining will not eliminate duplicates (and can create them).   You can see this by adding a second row in @UserList containing one of the values returned in your current dataset.  You may argue that this would never happen - but such arguments are often disproved when such queries are thrown at messy real live data. 

Another technique is to replace your outer-joins in the first query with the equivalent not-exists logic.  Same issue with duplicates, though.  One consideration for the joining approach is that it is possible - though unlikely - for NULL values to actually result in a join between the tables.  Simply turn ansi_nulls off.

set ansi_nulls off;
set nocount on;
declare @UserList table (Kerberos varchar(50) null);
insert into @UserList values ('A'), ('B'), ('C'), ('D'), ('D'), (null)
--select * from @UserList ;
declare @t1 table (Kerberos varchar(50) null);
declare @t2 table (Kerberos varchar(50) null);
insert @t1 values ('A'), (NULL);
insert @t2 values ('B');
-- query 1
select ul.Kerberos from @UserList ul
left outer join @t1 as a on a.Kerberos = ul.Kerberos
left outer join @t2 as b on b.Kerberos = ul.Kerberos
where a.Kerberos is null and b.Kerberos is null
-- query 2
select Kerberos from @UserList ul 
except 
select Kerberos from @t1 
except
select Kerberos from @t2

Answer 6

Naomi, how can both johnqflorida's answer and scott_morris-ga's be proposed as answer?  John's reply says they are both the same (except for the syntax error) and Scott's says they are different. 

BTW, I agree with Scott, they are only the same if the values in the Kerberos column in the @UserList table are both unique and not null - that is, that column is a candidate for a primary key of @UserList.

Tom

Answer 7

Yes, funny, isn't it? :) As these two queries are almost the same if the UserList has only unique entries and same for other two tables, then the queries will be the same.

Since I first read John's answer and liked it, I proposed it, then I liked Scott's answer even better...

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog