60

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


26

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


21

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


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


18

The manual clarifies here: An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead. That's according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a ...


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

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


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


13

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


13

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


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


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


11

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


11

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


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

I run this query: select * from t1 where c1 in (select c1 from t2); The above query should give an error as c1 is not present in t2. Instead, it returns all the rows from t1. No, the query should not give an error. It's a common mistake (thinking that the c1 in (select c1 from t2) refers to t2. It doesn't due to scope resolution, i.e. how column ...


10

After further research, I have discovered from this StackOverflow post that SQL Server breaks FOR JSON queries into "~2kb chunks". Sql Server splits result of FOR JSON query into ~2KB chunk, so you should either concatenate fragments like on the MSDN page or you can stream results into some output stream. This means that only ~2000 characters can ...


9

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


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

unless you're intending to return the fields as special_price.price and date.date why not alias the names inside the subquery? e.g. SELECT p.*, p.name AS name, p.image, p.price, ps.* FROM product p LEFT JOIN (SELECT psi.price as special_price, psi.date as my_date FROM product_special psi WHERE p.id = psi.id AND psi.date < ...


8

My colleague has found a way to change the query so that it needs a simple rewrite and does what it needs to do, i.e. doing the subselect in one step, and then doing the further operations on the result: SELECT mtid FROM publication WHERE mtid = ANY( (SELECT ARRAY(SELECT 9762715))::bigint[] ) OR last_modifier=21321 LIMIT 5000; The explain analyze now ...


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


7

The core of the problem becomes obvious here: Seq Scan on publication (cost=0.01..349652.84 rows=744661 width=8) (actual time=2735.888..2841.393 rows=1 loops=1) Postgres estimates to return 744661 rows while, in fact, it turns out to be a single row. If Postgres does not know better what to expect from the query it cannot plan better. We would need to ...


7

it is absolutely correct error, You are select single column in main select, so it must return only one column for avoid collisions for each column make a new sub-select: (SELECT username FROM users WHERE id = topicanswers.userid) as username (SELECT avatarurl FROM users ...


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;


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