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:
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 ...
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 ...
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 ...
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 '...
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 ...
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 ...
SELECT DISTINCT petid, userid, lastComDate, lastPosterId
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
AND comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH)
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 ...
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 ...
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:
tblUserContracts AS uc
LEFT JOIN tblOrderContracts AS oc
ON uc.UserContractID = oc.UserContractID
AND oc.OrderID = 466
Assuming combination product_special.id, product_special.priority is unique
SELECT p.*, special_price,special_date
FROM product p
SELECT ps.id, ps.price as special_price, ps.`date` as special_date
FROM product_special ps
SELECT id, MIN(priority) as min_priority
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
select products.id, products.name from
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 ...
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 * ...
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
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 ...
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 ...
Try something like this:
p.name as name,
p.id as id
products p inner join tag_ties ties
ties.tag_id in (10,11,12)
) as t1
count(t1.id) = 3
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 ...
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:
I'd probably use
SELECT TOP (1) WITH TIES PolicyNumber,
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
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)
The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply:
products AS p
tag_ties AS tt
tt.ref_id = p.id
tt.tag_id IN (10, 11, 12)
Extending this idea, we can also query based on the tag ...
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
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.
It means in order to determine whether subquery is correlated Oracle must try to resolve names in subquery ...
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 ...
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 ...
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 ...