CODEWITHSUNDEEP
sqlperformancesql-server-2008processing-efficiency
Saad Bhutto
Saad Bhutto

Reputation: 604

SQL Query efficiency (JOIN or Cartesian Product )

Hello I am Confused with three scenarios which commonly every one use in almost every project.

I wanted to Know which one of these will be Efficient accordinng to
- Less Time Complexity
- Efficiency
- effectiveness

TableA (userid ,username, email , phone)

TableB (username,TestField)

.

Case 1 

select email, TestField from TableA , TableB 
where TableA.username =  TableB.username  and 
TableB.username = 'ABC'
group by email, TestField

Case 2 

select email, TestField from TableA 
inner join TableB on TableB.username =  'ABC'

Case 3 

declare @uname nvarchar(20);
set @uname = 'ABC';
declare  @Email nvarchar(20);

select  @Email= email from TableA where username =   @uname; 

select  @Email as email , TestField from TableB 
where username = @uname

Upvotes: 0

Views: 956

Answers (2)

Achim Schmitz
Achim Schmitz

Reputation: 538

Most database experts will tell you that cross products are evil and to be avoided. Your first example would work just fine. It is an implicit inner join.

Your second example is syntactically incorrect. I suspect you'd get an error from MSSQL Server Manager. What you probably meant was:

select a.email, b.TestField 
from TableA a inner join TableB b 
  on (b.username = a.username)
where b.username = 'ABC'

Your first example will probably be the more efficient, since MSSQL Server is smart enough to do the projection on TableB.username before doing the join. I'm not so certain that this would be the case in the above version of case 2.

To be sure you could do it like this:

select a.email, b.TestField 
from TableA a inner join 
  (select * from TableB where TableB.username = 'ABC') b 
on (b.username = a.username)
where b.username = 'ABC'

Hope that helps.

Upvotes: 0

oerkelens
oerkelens

Reputation: 5151

Case 2 will give you a different output anyway, as you are not joining TableA and TableB in any way so you get a Cartesian product.

Since all of a sudden email came up, you will need a join in case 1:

In Case 1 you can simply rewrite the query to 

SELECT DISTINCT A.Email , B.TestField 
  FROM TableA A join TableB B on A.username = B.Username
 WHERE B.username = 'ABC'

Which is more readable and easier to maintain as you do not ave a superfluous GROUP BY clause.

In Case 3 you have userId in your where clause, which is not even in your tableB according to your post.

In general, for maintainability and readibility:

Use explicit joins

SELECT * FROM A JOIN B ON A.id = B.id

is preferable over 

SELECT * FROM A, B WHERE A.id = B.id

And use DISTINCT when you want distinct values, instead of GROUP BY over all columns:

SELECT DISTINCT a, b, b FROM TABLE

is preferable over

SELECT a, b, c FROM TABLE GROUP BY a, b, c

Upvotes: 1

Related Questions