CODEWITHSUNDEEP
sql
Zibian Domichi
Zibian Domichi

Reputation: 185

Sql query which one is faster

The first query is this

Select Name
     , Surname
     , ClassID 
  from classes
     , users 
 where classes.UserId = users.UserID 
    OR classes.UserID = 0

The second query is this

Select Name
     , Surname
     , ClassID 
  from classes 
  left 
  join users 
    on classes.UserId = users.UserID

The classes table have UserID 0 variable and i add the query OR classes.UserID=0

I don't have big data i can't try it. Someone can help me ? which is faster and why ?

Upvotes: 0

Views: 88

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

The queries are strikingly different.

The first is going to repeat user 0 for every class -- after doing an inner join. The second is going to keep all the classes, with a NULL value when the user doesn't match.

First, you should use the query that correctly implements the logic you want. Correctness is more important than performance.

Second, you should never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

Third, the second is probably what you want. It has the more reasonable result set.

Upvotes: 0

NotGI
NotGI

Reputation: 468

Second query is faster.

The first query combines all options from classes and users and then filters by the conditions specified in the where clause.

The second query filters while going through all the options.

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

Cross join { classes,users } gives you a cross product of records which is m X n and applying filter on that whereas other approach directly uses left join and gets the required data and hence second query will be faster and better

Upvotes: 1

Related Questions