Subquery versus traditional join with WHERE? Clause

Question

When connecting to a subset of a table, is there any reason to prefer one of these formats over another?

Subquery Version:

SELECT ... FROM Customers AS c INNER JOIN (SELECT * FROM Classification WHERE CustomerType = 'Standard') AS cf ON c.TypeCode = cf.Code INNER JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID 

against the WHERE clause at the end:

 SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON c.TypeCode = cf.Code INNER JOIN SalesReps AS s ON cf.SalesRepID = s.SalesRepID WHERE cf.CustomerType = 'Standard' 

The WHERE clause at the end seems more "traditional", but the first is perhaps more understandable, especially since the joins are becoming more complex.

The only other reason I can prefer the second is that "SELECT *" on the first can return columns that are not used later (in this case, I probably only need to return cf.Code and Cf.SalesRepID)

Answer 1

What about the third option?

 SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON cf.CustomerType = 'Standard' AND c.TypeCode = cf.Code INNER JOIN SalesReps AS s ON cf.SalesRepID = s.SalesRepID 

Personally, I prefer to use the JOIN syntax to specify operators for which a common set is defined, foreign keys or other conditions that specify two strings should be combined to make a string in the result set.

The WHERE contains criteria that filter my result set. It can probably become quite bloated and complicated when you do multiple joins, however, when you think that in sets it follows some kind of logic:

• SELECT which columns I want.
• JOIN tables to determine the set from which I want to get rows.
• Filter rows WHERE my criteria are not met.

By this logic, I would always choose your second syntax for consistent readability.

Answer 2

The second sentence is definitely more clear, and I suspect that the optimizer will also like it. And ideally, you should specify the columns you want.

Answer 3

The first version is a view. Do not confuse it with a subquery.

I would check the different versions in terms of performance (and making sure that they all give the same results, you will be surprised how often people forget to optimize the code, that the same results are important!). I suspect the first version was written in order to simulate the number of recordable entries as an attempt to improve performance (views often improve performance compared to other constructs and may replace a correlated subquery, which will almost certainly have better performance than). Regardless of whether it was or not, I would have to run in your db to see.

In principle, when two designs have the same result, I prefer to choose a faster one. Yes, it can be a little harder to understand (you can always add comments explaining what you did and why help those around you). But performance is one of the three most important things to consider when accessing a database (data security and integrity are the other two). Performance should ensure ease of maintenance in the database, especially for queries that run frequently. Avoid the extra 10 minutes to understand something once a year or so, when you need to look at it (and most requests are reviewed less), it does not cost extra seconds for each user every time he starts, especially when he starts in thousands once a day.

Answer 4

run

 SET SHOWPLN_ALL ON 

and then every request.

I would think that the first one can fulfill the same plan when in a simple query, but that the second will always work the same or better, especially in more complex queries.

Answer 5

I use only subqueries when there should be a separate query - for example, a group or something too complicated.

I would also make an option in the second query, for example:

 SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON c.TypeCode = cf.Code AND cf.CustomerType = 'Standard' INNER JOIN SalesReps AS s ON cf.SalesRepID = s.SalesRepID 

This will cut out the "extra lines" during the execution of the request part. It is not possible to change the optimizers for this query, but it will definitely be in others (external joins, additional subqueries, etc.)

Answer 6

As others said, the second option is better. But also consider the consequences of filter placement if you go to an external connection. If you might want to see all the customers and those customers who are classified as Standard, you need sales information, then check out the SQL below.

  SELECT ... FROM Customers AS c LEFT JOIN Classification AS cf ON c.TypeCode = cf.Code AND cf.CustomerType = 'Standard' LEFT JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID 

The code below will not produce the same results as above. It will have fewer lines and will be wrong.

  SELECT ... FROM Customers AS c LEFT JOIN Classification AS cf ON c.TypeCode = cf.Code LEFT JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID WHERE cf.CustomerType = 'Standard' 

But especially for your question, I would like to see the next version. I believe that in this version it is clear that in this version it is clear.

  SELECT ... FROM Customers AS c JOIN Classification AS cf ON c.TypeCode = cf.Code AND cf.CustomerType = 'Standard' JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID 

Answer 7

 SELECT ... FROM Customers AS c INNER JOIN (SELECT * FROM Classification WHERE CustomerType = 'Standard') AS cf ON c.TypeCode = cf.Code INNER JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON c.TypeCode = cf.Code INNER JOIN SalesReps AS s ON cf.SalesRepID = s.SalesRepID WHERE cf.CustomerType = 'Standard' 

SQL Server will process both queries the same.

These queries are identical in performance. You can freely exchange ON , WHERE and viewing conditions in a row: SQL Server Optimizer is smart enough to determine the best plan.

The first request is more easily converted to OUTER JOIN whenever the need arises, however in this case it can be better formulated as follows:

 SELECT ... FROM Customers AS c INNER JOIN -- or OUTER JOIN Classification AS cf ON cf.Code = c.TypeCode AND cf.CustomerType = 'Standard' INNER JOIN -- or OUTER JOIN SalesReps AS s ON s.SalesRepID = cf.SalesRepID 

When writing queries, I try to write them down so that the key character is obvious from the query.

If there is one column in cf.code , I would use the following:

 SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON cf.Code = c.TypeCode INNER JOIN SalesReps AS s ON s.SalesRepID = cf.SalesRepID WHERE cf.CustomerType = 'Standard' 

If the key is cf (Code, CustomerType) , then this one:

 SELECT ... FROM Customers AS c INNER JOIN Classification AS cf ON cf.Code = c.TypeCode AND cf.CustomerType = 'Standard' INNER JOIN SalesReps AS s ON s.SalesRepID = cf.SalesRepID 

and if the key is cf (CustomerType, Code) , then this one:

 SELECT ... FROM Customers AS c INNER JOIN ( SELECT * FROM Classification WHERE CustomerType = 'Standard' ) AS cf ON cf.Code = c.TypeCode INNER JOIN SalesReps s ON s.SalesRepId = cf.SalesRepID 

A quick note: in MySQL inline views are much less efficient than joins, so I won’t use them in MySQL in this case.

This does not apply to SQL Server .

Answer 8

I used 4 variants of Oracle DBMS, and they were more or less equivalent at runtime. I did not sing a complex subquery, but simply selected one column from the table (using subquery methods) and filtered this table in various ways (i.e., directly in the subselective / derived table or in the main where-clause or in terms of the join expression.

Answer 9

I always prefer the second option, because if you use the first one and the query becomes very complex, there can be a negative side to the performance.

Answer 10

I would say that in equivalent join / subquery situations, the optimizer is going to create similar execution plans, and the route you use should be determined by what ensures the most clear intent in the request. (e.g. select based on maintainability)

Answer 11

I would always go with the second, until I was forced to use the alternative.

Store joins in FROM and conditions in WHERE.