We’re rewarding the question askers & reputations are being recalculated! Read more.
61

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution. When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column. Finally the query is executed as: select a....


21

While I agree with other commenters that this is a computationally expensive problem, I think that there is a lot of room for improvement by tweaking the SQL that you are using. To illustrate, I create a fake data set with 15MM names and 3K phrases, ran the old approach, and ran a new approach. Full script to generate a fake data set and try out the new ...


20

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case). You can add the columns you ...


20

But the execution plan for both is same as shown below: The plans are different. One is an inner join, the other is an outer join. The results may be the same in your simple test, but the semantics are different. In more complex queries, the difference may cause more obviously different execution plans, and come with a performance impact. There are usually ...


17

With the index definition that you have for IDX_my_nme, SQL Server will be able to seek using the ActionDate column but not with the Address column. The index contains all of the columns needed to cover the subquery but it likely isn't very selective for that subquery. Suppose that almost all of the data in the table has an ActionDate value of earlier than '...


15

It's OK for nested subqueries to use the same aliases as used in the parent query, although it might be a bit confusing for someone reading the code. The name space for aliases on a nested subquery is separate from the name space on the parent. For example the query below has a nested subquery b that also has an alias b used within it. This would be ...


14

The subquery you have in your code is called a derived table. It's not a base table but a table that "lives" during the time that the query runs. Like views (which are also called viewed tables) - and in recent versions CTEs which is another, 4th way to "define" a table inside a query - they are similar to a table in many ways. You can select from them, you ...


12

SELECT DISTINCT petid, userid, lastComDate, lastPosterId FROM pet LEFT JOIN comments ON pet.id = comments.petid LEFT JOIN ( SELECT MAX(comDate), userid, petid FROM comments GROUP BY userid ) a ON a.petid = pet.id WHERE userid='ABC' AND deviceID!='ABC' AND comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH) ; ...


12

As already indicated in the comments it looks as though you need to update your statistics. The estimated number of rows coming out of the join between location and testruns is hugely different between the two plans. Join plan estimates: 1 Sub query plan estimates: 8,748 The actual number of rows coming out of the join is 14,276. Of course it makes ...


12

1. Subquery expression You can fix it with parentheses like @a_horse commented: SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1)); But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system ...


12

You can use a LEFT JOIN to get the same results. Note how the WHERE condition of the subquery was moved to the ON clause of the join: SELECT uc.UserContractId, uc.UserContractName, oc.OrderID FROM tblUserContracts AS uc LEFT JOIN tblOrderContracts AS oc ON uc.UserContractID = oc.UserContractID AND oc.OrderID = 466 ...


11

Assuming combination product_special.id, product_special.priority is unique SELECT p.*, special_price,special_date FROM product p LEFT JOIN ( SELECT ps.id, ps.price as special_price, ps.`date` as special_date FROM product_special ps INNER JOIN ( SELECT id, MIN(priority) as min_priority FROM product_special ...


11

You can solve this problem using intersect statements. Do a separate select for each tag_id and join them with intersects and you'll only get the records that match all three tag_ids. select products.id, products.name from products join tag_ties on tag_ties.ref_id = products.id where tag_ties.tag_id = 10 intersect select products.id, products.name from ...


11

Just to provide some additional explanation to billinkc's answer. If null is a trump card you might be wondering why doesn't WHERE 2 IN (2,3, NULL) exhibit the same behavior? That one works as expected because it evaluates to (2=2) OR (2=3) OR (2=NULL). Under the rules of three valued logic for Or-ed conditions if any of them evaluate to true the ...


10

Try: where exists (select * .... where Customers.orderId = ... or Customers.secondaryId = ... ) Eg, if you were planning on: where orderId in (select value from ...) or secondaryorderid in (select value from ...) Then you make it so that you only call your subquery once, and build your OR clause into it. where exists (select * ...


10

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause. Your second query can be rewritten without derived tables by simply moving that condition to the on clause: select x.*, y.* from factI as x left join factI as y on x.tickedId = y.tickedId and y....


10

If id is defined as the primary key, you can omit grouping by all the foo columns you want for the output as long as you are grouping by the id. This special case of grouping is in accordance with the current SQL standard and has also been covered in the PostgreSQL manual, starting from version 9.1: When GROUP BY is present, or any aggregate functions are ...


10

Posting the query plan for the two versions of the query (and the exact SQL for the other version just for clarity's sake) would certainly be helpful. I would guess that doing so would prove out the following theory. Without it, though, I can guess what is likely going on but I can't be sure. In general, the database is free to evaluate the elements of a ...


9

Try something like this: select t1.id, t1.name from ( select p.name as name, p.id as id from products p inner join tag_ties ties on p.id=ties.ref_id where ties.tag_id in (10,11,12) ) as t1 group by t1.id, t1.name having count(t1.id) = 3 order by t1.name asc ;


9

Blowing up my comment with an example. It really doesn't matter whether you're using a GUID or any other data type. Your filter should evaluate to a true or false condition. In .net/python/java/etc that's what you get. In the SQL world, we get to have tri-state built into everything. It's true, it's false, it's... I don't know. When a NULL value enters the ...


9

MySQL forbids referencing outer-level columns deeper than one level of nesting. Your query, however, is referencing users.id three levels deep. What you need, therefore, is to rewrite the correlated subquery in such a way that, even if it uses nested queries, the correlation with the outer level is not nested, something like this: ( SELECT COUNT(*) ...


8

I'd probably use SELECT TOP (1) WITH TIES PolicyNumber, decpageid, Risk FROM StatRiskDecpages WHERE PolicyNumber = 'AR-0000301132-04' ORDER BY decpageid DESC Assuming the covering index on (PolicyNumber, decpageid) INCLUDE(Risk) this will give you a plan like


8

In MySQL, subselects within the IN clause are re-executed for every row in the outer query, thus creating O(n^2). The short story is, don't use IN (SELECT).


8

In Query 3, you are basically executing a subquery for every row of mybigtable against itself. To avoid this, you need to make two major changes: MAJOR CHANGE #1 : Refactor the Query Here is your original query Select count(*) as total from mybigtable where account_id=123 and email IN (select distinct email from mybigtable where account_id=345) You ...


8

The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply: SELECT p.* FROM products AS p INNER JOIN tag_ties AS tt ON tt.ref_id = p.id AND tt.tag_id IN (10, 11, 12) GROUP BY p.id HAVING COUNT(p.id)=3 Extending this idea, we can also query based on the tag ...


8

I have rather bad news for you in this regard MySQL Query Optimizer tends to stray away for further optimization once it sees a FULLTEXT index. I have written about this before in the StackExchange May 23, 2011 : https://stackoverflow.com/a/6092216/491757 Oct 25, 2011: FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional Jan 26,...


8

Because Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357 It means in order to determine whether subquery is correlated Oracle must try to resolve names in subquery ...


8

Do you have a reason to believe that they will perform differently? If so, why not test them on your RDBMS and with your data? In general I would say to start with the simplest query, test performance, and only attempt something more complicated (such as option 2) if necessary. You didn't list an RDBMS so I'll give a SQL Server example. The SQL Server query ...


8

The odds are that your problem is that, when you add in the snacks, you have more than one table that returns more than one row for the columns in your GROUP BY statement - probably Customer_Snacks and Customer_Ledger_LineItems. When the DB engine performs a join, it matches however many rows in the first table with value ABC with however many rows in the ...


8

If you change the left join with dbo.Haul to a subquery, it will calculate these distinct values of ID_DestinationAddress (Stream Aggregate) and Count them (Compute scalar) directly after getting the data from the scan. This is what you are seeing in the execution plan: While, when using the GROUP BY method it is only doing the grouping after data passed ...


Only top voted, non community-wiki answers of a minimum length are eligible