Hot answers tagged

36

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 ...


13

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 ...


11

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 ...


10

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 ...


10

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 ...


10

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 ...


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....


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 ;


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

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 : http://stackoverflow.com/a/6092216/491757 Oct 25, 2011: FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional Jan 26,...


8

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 ...


8

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 ...


7

If you want to use a SELECT statement where only a single value is allowed you need to put that SELECT statement into brackets: insert into dates (d) values ( (select sysdate from dual) ) That could be extended for multiple columns: insert into dates ( id, d, other_column ) values ( some_sequence.nextval, (select sysdate from dual), (...


7

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 * ...


7

Add an alias for the derived table before the ; for example VendLargestUnpaidInv: Select Sum(LargestUnpaid) from (Select Max(InvoiceTotal) AS LargestUnpaid from Invoices where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) VendLargestUnpaidInv;


7

You can do it like this: select * from dbo.Invoice I1 join dbo.Vendors V1 on V1.VendorID = I1.VendorID where I1.InvoiceTotal > ( SELECT Avg(I2.InvoiceTotal) from dbo.Invoices I2 join dbo.Vendors V2 on V2.VendorID = I2.VendorID where V1.VendorState = V2.VendorState ...


6

Here's how I refactored your query. (first I'll post your query with your execution plan, and then follows is my new query and the corresponding query plan) Your Query Select t1.PolicyNumber, t2.DecpageID, t2.Risk from ( select PolicyNumber, MAX(decpageid) as decpageid from StatRiskDecpages where PolicyNumber =...


6

You need to change your subquery approach slightly - moving the condition from the WHERE clause to a join, to bypass MySQL limitations. If the id and creation_time always define the same ordering, you can use this: DELETE p FROM pixels AS p JOIN ( SELECT id FROM pixels ORDER BY id LIMIT 1 OFFSET 4 ...


6

(SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' ) This is a condition, but you need n values. This should work: ( ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) ...


6

Just out of curiosity, can you try this version? It may trick the optimizer to use the same indices that the subqueries would use separately: SELECT * FROM (SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit, 'hp' AS source FROM is_log AS l WHERE l.account_id = 730 ORDER BY l.zeitpunkt DESC LIMIT 10) AS a UNION ALL SELECT * ...


6

No. But almost. There are four things that joins do. I wrote about this at: JOIN simplification in SQL Server Your "NOT IN" (which you should be careful of, regarding NULLs - try using NOT EXISTS) won't duplicate any rows. A LEFT JOIN can. But consider the use of Unique Indexes/Constraints/PKs, which can help the Query Optimizer treat these as identical. ...


6

You don't need all the derived tables. You are joining the basic (product) too many times. You can write the query joining it only once. Compound indices are a must for EAV designs. Try adding an index on (attribute_id, product_id, value) and then the query: SELECT t0.id, t1.`value` AS length, t2.`value` AS height, t3.`value` AS ...


6

You can't reference an alias in the WHERE clause - this is just because of the order in which SQL Server parses the statement. There have been many discussions about this here and on StackOverflow. A couple of examples that give some background: Why is the SELECT clause listed first? Why are queries parsed in such a way that disallows the use of column ...


6

Ignoring the lessons about NULL (which you should observe), NOT IN is a horrible choice for a left anti-semi join. Try: select a.guid from dbo.tableA AS a where not exists (select 1 from tableB AS b where b.guid = a.guid) As an aside, your not in version mentioned b.guid, but I see no table aliased as b.


6

The elegance of the answer will vary with the DBMS of your choice. In it's simplest form: select name, city as address from temp1 union select name, phone_no as address from temp1 union select name, pincode as address from temp1 I believe this should be supported by most DBMSes. If your DBMS supports lateral (cross apply in sqlserver?) you can do ...


6

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 ...


6

It means the number of columns you select in the top query must be the same as the number of columns in the second. If you don't have the same number, you can work around it. Below I can add a NULL to the second query because it's missing a third column. SELECT col1,col2,col3 FROM t1 UNION ALL SELECT col1,col2, NULL FROM t2


6

Let's shelve the obvious issue brought up by Aaron Bertrand in the comments for a second: So you are scanning the table 3K times, and potentially updating all 15MM rows all 3K times, and you expect it to be fast? The fact that your subquery uses the wild cards on both sides dramatically affects sargability. To take a quote from that blog post: ...



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