I am trying to create a SQL query that will SELECT columns from table 2 based on equality between one of two columns in table 1.
I understand I can't JOIN on multiple columns. Conceptually I want it to JOIN on a common column, call it column 1, if column 1 exists (not NULL) in both tables, else JOIN on a different common column, call it column2.
Since a JOIN like this isn't legal, I came up with an alternative with multiple COALESCE's but it is far too slow for my purposes (there will be a lot of rows in those tables):
The two WHERE clauses represent checking if the first column exists or not in both tables and then checking the second column.
My query will need to select n columns from table2. How it is currently I have to add n COALESCE statements, which is really inefficient.
Does anyone know a way of doing this query all at once, i.e. without coalescing multiple times? Or another way of making it performant?
Minimum reproducible example:
/* Definition query */
CREATE TABLE [dbo].[table1](
[foo] [bigint] NULL,
[bar] [nvarchar](50) NULL,
[qux] [bigint] NULL,
[quux] [nvarchar](50) NULL
);
CREATE TABLE [dbo].[table2](
[baz] [nvarchar](50) NULL,
[qux] [bigint] NULL,
[quux] [nvarchar](50) NULL,
[corge] [bigint] NULL
);
/* Sample data */
INSERT [dbo].[table1] ([foo], [bar], [qux], [quux]) VALUES (1, N'asd', 345, NULL);
INSERT [dbo].[table1] ([foo], [bar], [qux], [quux]) VALUES (2, N'fas', NULL, N'abc');
INSERT [dbo].[table1] ([foo], [bar], [qux], [quux]) VALUES (3, N'fasfdjka', 678, NULL);
INSERT [dbo].[table1] ([foo], [bar], [qux], [quux]) VALUES (4, N'jggiy', NULL, N'def');
INSERT [dbo].[table2] ([baz], [qux], [quux], [corge]) VALUES (N'afsfsaf', 345, N'xyz', 764694659);
INSERT [dbo].[table2] ([baz], [qux], [quux], [corge]) VALUES (N'jjuiku', 8910, N'abc', 519285912);
INSERT [dbo].[table2] ([baz], [qux], [quux], [corge]) VALUES (N'gghsd', 678, N'vuw', 152512512);
INSERT [dbo].[table2] ([baz], [qux], [quux], [corge]) VALUES (N'oolas;p', 111213, N'def', 921839129);
/* Select query */
SELECT t1.foo, t1.bar ,
COALESCE(
(
SELECT top 1 t2.baz
FROM table2 as t2
WHERE t2.qux = t1.qux
),
(
SELECT top 1 t2.baz
FROM table2 as t2
WHERE t2.quux = t1.quux
)
) AS baz,
COALESCE(
(
SELECT top 1 t2.corge
FROM table2 as t2
WHERE t2.qux = t1.qux
),
(
SELECT top 1 t2.corge
FROM table2 as t2
WHERE t2.quux = t1.quux
) ) AS corge
FROM table1 as t1
Here is the result I am getting, which is the desired result. But when you operate on large tables it is far too slow, which makes it inviable.
I tried INTERSECT in place of COALESCE but it gives me this result with NULL's in both columns I trying to select from table 2, which is not desired:
SELECT t1.foo, t1.bar ,
(SELECT top 1 t2.baz
FROM table2 as t2
WHERE t2.qux = t1.qux
INTERSECT
SELECT top 1 t2.baz
FROM table2 as t2
WHERE t2.quux = t1.quux)
AS baz,
(SELECT top 1 t2.corge
FROM table2 as t2
WHERE t2.qux = t1.qux
INTERSECT
SELECT top 1 t2.corge
FROM table2 as t2
WHERE t2.quux = t1.quux)
AS corge
FROM table1 as t1
I tried using EXCEPT instead of COALESCE and it gave me a different result, but not a desired one:
SELECT t1.foo, t1.bar ,
(SELECT top 1 t2.baz
FROM table2 as t2
WHERE t2.qux = t1.qux
EXCEPT
SELECT top 1 t2.baz
FROM table2 as t2
WHERE t2.quux = t1.quux)
AS baz,
(SELECT top 1 t2.corge
FROM table2 as t2
WHERE t2.qux = t1.qux
EXCEPT
SELECT top 1 t2.corge
FROM table2 as t2
WHERE t2.quux = t1.quux)
AS corge
FROM table1 as t1
Thanks