The meaning of life is to explore the world

SQL traps - DISTINCT and IN clauses

Posted on Oct 18, 2021 By Jason Liu

Test your knowledge with below questions:

--What are the outputs?

IF 3 IN (SELECT 3 UNION SELECT 4 UNION SELECT NULL)
	PRINT 'TRUE'
ELSE
	PRINT 'FALSE'
	
IF 5 NOT IN (SELECT 3 UNION SELECT 4 UNION SELECT NULL)
	PRINT 'TRUE'
ELSE
	PRINT 'FALSE'
		
IF 5 NOT IN (SELECT DISTINCT Col FROM (SELECT 3 Col UNION SELECT 4 UNION SELECT NULL) T)
	PRINT 'TRUE'
ELSE
	PRINT 'FALSE'
	
SELECT COUNT(DISTINCT Col) FROM (SELECT 3 Col UNION SELECT 4 UNION SELECT NULL) T

Answers:

--Answers:

--TRUE
--FALSE
--FALSE
--2