SQL Server Performance Forum – Threads Archive

Multi-table join vs. single queries

Three tables (A, B and C) have the same key, which is known up front. From an efficiency standpoint, which is better? Multi-Table Join into single Recordset
SELECT *
FROM TableA A, TableB B, TableC C
WHERE A.key = 1 AND B.key = 1 and C.key = 1 OR 3 Single Joins into their own Recordsets
SELECT * FROM TableA WHERE key = 1
SELECT * FROM TableB WHERE key = 1
SELECT * FROM TableC WHERE key = 1 Also, would having to LEFT OUTER JOIN tables B & C change the answer (because they may not always be populated)? Thanks.

What does Query Execution plan says? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

The first statement will give you the cartesian product of the data in thr three tables which I’m sure you don’t want. The only solution that i see is SELECT * FROM TableA WHERE key = 1
UNION
SELECT * FROM TableB WHERE key = 1
UNION
SELECT * FROM TableC WHERE key = 1
And if you are sure that the data across tables is going to be unique, you can go for UNION ALL inplace of UNION as it has better performance. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Why don’t you try it? set nocount on declare @tableA table (i int) declare @tableB table (i int) declare @tableC table (i int) insert into @tableA select 1 union all select 444 insert into @tableB select 1 union all select 22 insert into @tableC select 1 union all select 333 SELECT * FROM @tableA A, @TableB B, @TableC C WHERE A.i = 1 AND B.i = 1 and C.i = 1 SELECT * FROM @tableA WHERE i = 1 SELECT * FROM @TableB WHERE i = 1 SELECT * FROM @TableC WHERE i = 1 Change the values as you want, and check execution plan and results. You will learn more trying than from just reading the answers <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Bambola.

Let’s assume I’m using a database that doesn’t have the Execution Plan Tool and want to know the answer from a SQL standpoint, not Microsoft SQL Server. The results I want will be in a single row in the recordsets, so the UNION options are out. I just want to know whether its more efficient to query the database once with a 3 table join or query it 3 times accessing a single table (all queries by the primary key)…

Also, please assume that the key I’m referring to is the Unique Primary Key. The first example would return 1 row only (which is stored in a recordset for use later in the code). The second example would return 1 row for each query (which I will store in it’s own unique recordset for use later in the code). I’m not looking for a "try it", because there could be many factors, such as the table size, the type of index (clustered or not), or other things. I’m looking for and answer like, "It’s more efficient to do multi table joins for small table, but if the tables exceed X number of rows, the joins become inefficient, making the single queries the better option"… I can’t test every possible combination of factors, so I’m hoping we have an advanced query expert in the group.

Definately querying the database once will be the best option. The more times you query, the more times you’ll have to make network trips, which are costly. Hope this answers your question. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

If the problems is having only one recordset, you can solve it like this SELECT
(SELECT field_name FROM TableA WHERE key = 1) a,(SELECT field_name FROM TableB WHERE key = 1) b,
(SELECT field_name FROM TableC WHERE i = 1) c Bambola.

Discussion continued onhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1542. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

]]>