A puzzle, A quick query puzzle, A SQL Puzzle, A SQL Server Puzzle, Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Join SQL Server tables where columns include NULL values, Joins, Joins Interview questions, Joins Puzzle, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, NULLs and JOINs, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, puzzle sql developer, Puzzles, Puzzles in SQL, PUzzles in SQL SERVER, Queries for SQL Interview, SELECT, SELECT Puzzle, Some useful SQL puzzles to teach SQL, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL puzzle, SQL Puzzles, SQL Queries, SQL QUERY PUZZLES, SQL Quiz, SQL Server - Best way to Handle NULLS in Joins, SQL Server Database, SQL SERVER Interview questions, SQL Server Puzzle, SQL SERVER Puzzles, SQL Server: joining NULL values, SQL Skills, SQL Sudoku, SQL Top clause, SQL Tricky Puzzles, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, The Biggest Gap Puzzle, The Gap Puzzle, The GroupBy Puzzle, Tough SQL Challenges, Tough SQL Puzzles, Tricky Puzzles in SQL, tricky puzzles in SQL Server, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzles, TSQL Queries, TSQLPuzzles, Week puzzle
SQL Server – Best way to Handle NULLS in Joins
Well today we will talk about how to handle null values while joining tables, especially in case of inner joins when you want them to match. If you see online there some methods given but none of them mentioned which one is the best. It actually depends on the kind of query and the kind of data you have in your tables. I will show you some methods you can use to join null values while joining tables.
Let’s create some sample data.
-- CREATE TABLE testJoinswithNULLs1 ( ID INT ) GO INSERT INTO testJoinswithNULLs1 VALUES ( NULL ) CREATE CLUSTERED INDEX Ix_Id ON testJoinswithNULLs1(ID) CREATE TABLE testJoinswithNULLs2 ( ID INT ) GO INSERT INTO testJoinswithNULLs2 VALUES ( NULL ) GO 1000 INSERT INTO testJoinswithNULLs2 VALUES (1),(2),(3),(4),(5) CREATE CLUSTERED INDEX Ix_Id ON testJoinswithNULLs2(ID) --
Here both the tables have clustered keys associated with it. In first table we have a single null value and in the second table we have 1000 null values.
Method 1. Use of ISNULL function – Here we just capturing the NULL value using ISNULL() function @ run-time and replacing it by -1, -1 is just and example, You can use any value you want. The value you select should not be present in the table you are joining.
-- SELECT a.ID , b.ID FROM testJoinswithNULLs1 a INNER JOIN testJoinswithNULLs2 b ON ISNULL(a.ID,-1) = ISNULL(b.ID,-1) GO --
Method 2. Use of COALESCE function – Here we just capturing the NULL value using COALESCE() function @ run-time and replacing it by -1, -1 is just and example, You can use any value you want. The value you select should not be present in the table you are joining.
-- SELECT a.ID , b.ID FROM testJoinswithNULLs1 a INNER JOIN testJoinswithNULLs2 b ON COALESCE(a.ID,-1) = COALESCE(b.ID,-1) GO --
Method 3. Check for NULL while Joining – Here we are checking for NULL Values using OR condition
-- SELECT a.ID , b.ID FROM testJoinswithNULLs1 a INNER JOIN testJoinswithNULLs2 b ON a.ID = a.ID OR (a.ID IS NULL AND b.ID IS NULL) GO --
Now let’s check out the execution plans for all the three methods.
Now I know that the statistics are up to date, hence we can trust the cost. It is clearly evident that the last method is the best one we have. So choose wisely.:)
Cheers, Thanks for reading !