I would say always aim for join. If this is not possible consider subquery or CTE. Here is why Query order processing is: FROM, WHERE, GROUP BY, SELECT... Sooner you narrow result is better since next processed clause will have less to do. Using join like this: SELECT ... FROM users u JOIN purchase p ON u.id=p.id ... will narrow result in the first processed clause - FROM clause. It will return only records satisfying join condition. In this example only users with at least one purchase. In WHERE clause you narrow further. E.g. WHERE users.name like 'Dr%' Or WHERE region.country='c' Next there are GROUP BY, ORDER BY and FETCH. E.g. ... ORDER BY purchase.price FETCH NEXT 100 ROWS ONLY Optimizer is sophisticated piece of software. Sometimes it can rewrite a query from CTE or subquery into join. I do not know if MySQL's is smart enough though.

An alternative way to Lennart's window function answer, is to just use a GROUP BY and HAVING clause against the peoples table to filter out the ones with the same postcode like so: SELECT towns.id, towns.town, peoples.names FROM towns LEFT JOIN ( SELECT MAX(names) AS names, postcode FROM peoples GROUP BY postcode HAVING COUNT(name) = 1 ) peoples ON towns.postcode = peoples.postcode

The horrible estimation of 'transfers' condition (expected 2600, found 1) caused the cost of the hash join and the nested loop to appear falsely similar. Why does misestimating by 2600 fold cause one query to use a different plan but not the other? No idea, probably just luck. I doubt it is even reproducible, much less interesting. Fix the root problem. If an ANALYZE transfers doesn't fix it, then you will need to dig into pg_stats for column "from_name".

use row_number() to find the row with the highest resourceid. And then filter it at the outer query select running, resourceid, item from ( select *, rn = row_number() over (partition by running order by resourceid desc) from runningResources ) as a where a.rn = 1

it's better to use INNER JOIN instead LEFT JOIN, but if you don't want to change your query, you can add this at the end of your query: WHERE p.product_id = 1 AND m.material_label IS NOT NULL AND v.variation_label IS NOT NULL;

I'm afraid that there's no NATURAL JOIN or any other language element so you don't have to specify the join condition in MSSQL. (I wish that exists...) As an aside, if you stick to the old join syntax, you won't be able to do OUTER JOINs in MSSQL. (I know you didn't ask for this, but thought I'd mention this part anyhow...)

Add these composite indexes: ad_camp_sid: INDEX(sid, cid) ad_dsp_sites: INDEX(exchange, site_id) When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.

The parts of an SQL statement are processed in a certain order (at least logically). In the case at hand, the WHERE condition is evaluated before a window function like row_number(). Your view definition and the query on the view reverse that order by effectively introducing a subquery: SELECT * FROM (SELECT ROW_NUMBER () OVER () AS id, t1.segment_id AS segment_id, t1.activity_id AS activity_id, t1.measured_date AS from_date, t2.measured_date AS to_date, t2.value AS cumulative_progress, (t2.value - t1.value) AS marginal_progress, FROM sample AS t1 JOIN sample AS t2 ON t1.activity_id = t2.activity_id AND t1.segment_id = t2.segment_id and t1.measured_date < t2.measured_date ) AS sample_marginal WHERE segment_id = '00021c8d-7162-467d-8e6a-4cb62926bf53' AND activity_id = '34a4b908-4613-422a-b6d0-4bb112737f09' ORDER BY from_date asc, to_date asc; There is nothing that the optimizer can do about that. If you remove the window function call (that seems pretty pointless anyway) from the view definition, performance should improve.

This can be accomplished simply with a https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15 and https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15 clause like so: SELECT p.designation, SUM(ISNULL(sp.quantity, 0)) AS [total quantity] FROM product AS p LEFT JOIN sub_product AS sp ON p.id = sp.product_id GROUP BY p.id, p.designation Note the use of a LEFT OUTER JOIN (aka a LEFT JOIN) to ensure that even products without any sub_products are still returned (with a total quantity of 0). If it's guaranteed there'll always be at least 1 sub_product for every product then you can use an INNER JOIN instead.

without OR IS NULL? col = 'x' OR col IS NULL Original: SELECT * FROM a FULL JOIN b ON a_id = b_id WHERE (a_prop = 'blah' OR a_prop IS NULL) AND (b_prop = 'blah' OR b_prop IS NULL); Use https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIONS-COMPARISON-PRED-TABLE : SELECT * FROM a FULL JOIN b ON a_id = b_id WHERE a_prop <> 'blah' IS NOT TRUE AND b_prop <> 'blah' IS NOT TRUE; Or filter before joining: SELECT * FROM (SELECT * FROM a WHERE a_prop = 'blah') a FULL JOIN (SELECT * FROM b WHERE b_prop = 'blah') b ON a_id = b_id; col <> 'x' OR col IS NULL The first version of the question asked for this predicate. Original: SELECT * FROM a FULL OUTER JOIN b ON a_id = b_id WHERE (a_prop <> 'not this one' OR a_prop IS NULL) AND (b_prop <> 'not this one' OR b_prop IS NULL); Use https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIONS-COMPARISON-PRED-TABLE : SELECT * FROM a FULL JOIN b ON a_id = b_id WHERE a_prop IS DISTINCT FROM 'not this one' AND b_prop IS DISTINCT FROM 'not this one'; Or filter before joining: SELECT * FROM (SELECT * FROM a WHERE a_prop <> 'not this one') a FULL JOIN (SELECT * FROM b WHERE b_prop <> 'not this one') b ON a_id = b_id; db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=945f8ce50619f40f2a550a083f049946 - showing all Aside: Instead of != I use <>, which is the standard operator in SQL. (!= is an accepted alias in Postgres.)

If you were to upgrade to at least MySQL 8.0, you'd be able to take advantage of https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html : create index order_ordered on order (company_id, id desc); SELECT lateral_o.id, lateral_o.company_id, lateral_o.total FROM company c LATERAL (select o.id ,o.company_id ,o.total from order o where c.id = o.company_id order by o.company_id, o.id desc limit 25 ) lateral_o WHERE c.company_headquarter_id = 23133 ORDER BY lateral_o.id DESC LIMIT 25; This will do what it looks like, get the top 25 rows for each company_id value you find using an index, then do a final sort at the end. The current plans you have will either be: going through the order table in reverse id order and checking if the company belongs to your filter until it succeeds 25 times. OR, it will be going to every single matching order row, sorting and returning the 25 desired. The first choice is fine if the chance of an order being one you care about is high, but awful if it's not. The second choice is great if there's only a few matching order rows, but will get worse when there is lots. My suggestion's performance will be related to how many company rows you identify, and it sounds like there won't often be many.

What you're looking for is a recursive query: with recursive nodes(id, path) as ( -- selecting the parent/root (anchor) select r.id, r.name from mytable r where r.id = union all -- recursively going through child nodes (recursive member) select c.id, concat(path, '->', c.name) from mytable c join nodes as n on n.id = c.parent_id ) select * from nodes order by path; Keep in mind that if you have self referencing members (or ones that reference each other in a circle), this will go into an infinite loop! So you have to either exclude such scenarios with a where statement or not allow any inserts/updates that would cause such a scenario.

Firstly (although it doesn's apply in this case), you should always include your version of PostgreSQL with any questions here. What you are referring to is an [INNER] JOIN. The different types of joins are explained https://www.geeksforgeeks.org/postgresql-joins/ . There are many links on different join types out there. So, in this case, I would do the following: CREATE TABLE my_user ( id INT NOT NULL, age INT NOT NULL, PRIMARY KEY (id) ); and CREATE TABLE connection ( connection_id INT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (connection_id), CONSTRAINT conn_user_fk FOREIGN KEY (user_id) REFERENCES my_user (id) ); populate them: INSERT INTO my_user VALUES (1, 50), (2, 45), (3, 73); and INSERT INTO connection (user_id, connection_id) VALUES (1, 500), (1, 501), (1, 502), (1, 503), (1, 504), (2, 601), (2, 602), (2, 603), (2, 604), (2, 605), (3, 701), (3, 702), (3, 703), (3, 704); And then run the following SQL: SELECT u.id, u.age, c.connection_id, c.user_id FROM my_user u JOIN connection c ON u.id = c.user_id; Result: id age connection_id user_id 1 50 500 1 1 50 501 1 1 50 502 1 1 50 503 1 1 50 504 1 2 45 601 2 2 45 602 2 2 45 603 2 2 45 604 2 2 45 605 2 3 73 701 3 3 73 702 3 3 73 703 3 3 73 704 3 So, you have all the information from both tables. If you have more fields, you may wish to include or exclude them as per your requirements. One word of strong advice. Do NOT store people's ages. Rather, store their birthday and calculate their age from that on the fly! For example: CREATE TABLE my_user_bis ( id INTEGER, bday TIMESTAMPTZ ); 1 record as a sample: SELECT *, AGE(CURRENT_DATE, bday::DATE), EXTRACT(YEAR from AGE(bday)) FROM my_user_bis; Result: id bday age date_part 1 2000-05-05 00:00:00+01 21 years 11 mons 16 days 21 If you don't store birthdays and do a calculation on the fly, you'll start to get errors - what happens if someone who's 53 now record is accessed in 3 years time - it'll still show him as 53. Unless you implement some sort of horrible hack with a trigger running at the end of each year! Yuck! My advice is to store birthdays - if the user is happy to provide their age, there's no reason that they wouldn't provide a birthday! Welcome to dba.se!

Using inner join, starts from the Parts table and self join to get the Parent's information. select e.EquipmentNumber, p.PartNumber, ChildPartNumber = c.PartNumber, c.CTypeId, c.VendorId from Parts c -- child inner join Parts p on c.MainPartId = p.Id -- parent inner join EquipmentPartAssoc a on p.Id = a.PartId inner join Equipment e on e.Id = a.EquipmentId where c.CTypeId = 441 and c.VendorId = 'V1' Result : EquipmentNumber PartNumber ChildPartNumber CTypeId VendorId EQL335 TR001 GR01-4324 441 V1 EQL335 CS001 GR01-378 441 V1 EQL873 CS001 GR01-378 441 V1 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6013ab43e761a362d7d3538d5a61f2ae

When you use the GROUP BY clause, your result set will contain one row per unique value combination of the fields you're grouping on. Your query groups on the sales.sales_id field and in your example dataset you have multiple different sales_ids. That is why you're getting multiple rows back, and the SUM() of the data in each row is only across the source rows of that row's sales_id. Since you're not SELECTing those fields, and it sounds like you want a single SUM() across all rows, you don't need to use a GROUP BY clause here. The following query without it should be all you need: SELECT SUM(DISTINCT `sales`.`sale_shipping`) as `total_shipping` FROM `sales` LEFT JOIN `contacts` ON `sales`.`contact_id` = `contacts`.`contact_id` LEFT JOIN `salespayments` ON `salespayments`.`sale_id` = `sales`.`sale_id` LEFT JOIN `contactsadditionalreps` ON `contacts`.`contact_id` = `contactsadditionalreps`.`contact_id` WHERE `salespayments`.`payment_type`!='Refund'; Also, side note, because you're explicitly filtering on WHERE salespayments.payment_type !='Refund'; then you don't need to use a LEFT (outer) JOIN on salespayments. Every row has to match on the join in order for salespayments.payment_type to not be null. Therefore you can use an INNER JOIN instead, which may be more efficient in some cases.

I would solve this with a sub-query. I think that would be the best route available to you. As soon as you JOIN you have expanded the results to be at the granularity of the opportunity. It would be easier to never expand to this granularity and instead retrieve the counts from a subquery: Select Ac.frm_GEO__c, [SummarizedOpportunity].Amt ,Rank () Over (Order By [SummarizedOpportunity].Amt Desc) as 'Rank' ,sum(ac.NumberOfEmployees) num_empl FROM Account Ac JOIN (SELECT [Opportunity].[AccountId], [Amt] = SUM([Opportunity].[Amount]) FROM [Opportunity] GROUP BY [Opportunity].[AccountId]) [SummarizedOpportunity] ON [SummarizedOpportunity].[AccountId] = Ac.Id Where Ac.Name like 'Brinks%' Group by Ac.frm_GEO__c

A CASE expression returns a single value. It can't return an identifier (e.g. a table reference). And its value can't be used instead of a table name The typical way to deal with this kind of optional dependencies, is to join to each table and include the condition that picks a table in the join conditions. In order to get rows back, you will need an outer join because you can't know in which table there will be a match SELECT item_table, item_id, * FROM item_instances AS ii LEFT JOIN item_templates it ON ii.item_id = it.item_id and ii.item_table = 0 LEFT JOIN unique_items ui ON ii.item_id = ui.item_id and ii.item_table = 1 You can then use coalesce() in the SELECT list, to get the non-null values from either table.

The JOINs are done first, then the GROUP BY together with its aggregates, such as SUM. If you want the SUM to be done against a single table, then the GROUP BY needs to be against that one table. Maybe: ... FROM ( SELECT sale_id, sale_shipping FROM sales GROUP BY sale_id ) AS x JOIN ...

Optimizing IO is often a good starting point, but in your case all of your buffers are "Hit", so there is nothing there to optimize. You can denormalize your data "automatically" by using a functional index. This does involve declaring a function as "immutable" when it might not truly be immutable. So there is a risk there that you might get mild corruption (wrong answers from queries using the index) if orders.scan_timestamp ever changes after it was already used in order_products. You could try to mitigate this with some trigger on orders, but then you would probably have race conditions of both tables are being changed at the same time. create function get_scan(int) returns timestamptz language sql immutable parallel safe as $$ select scan_timestamp from orders where order_id=$1 $$; create index on order_products (pick_number , get_scan(order_id); Then the corresponding query would be: SELECT sum(quantity) FROM order_products WHERE get_scan(order_id) >= '2022-04-01T00:00:00.000000+02:00' AND get_scan(order_id) < '2022-04-02T00:00:00.000000+02:00' AND pick_number = '8853'; You could instead use an actual column to order_products, using the same function define above (with the same caveat about immutability) in a GENERATED expression, and then use a regular (not function-based) index including that column. A downside to that is that you are storing the denormalized data an extra time, both in the table and in the index. Plus you now have a column showing up in the table that would need to be documented as not really belonging there. The benefit though is that you could get an index-only scan in situations where you might not be able to get one with functional index.

I don't know who Greg Rahn is, but that's one ugly query, especially with the stuffing of the predicates of the JOINs into the WHERE clause instead of on a proper JOIN clause between each referenced object. That's a non-standard syntax, and I recommend not following it. Order of the objects being JOINed depends on the database system we're talking about. Some do explicitly care about the order of the tables (I believe generally by the cardinality of the data set of that object) in order to achieve the most optimal plan but most don't care and automatically try to optimize the JOIN order themselves, such as Microsoft SQL Server. Of course the complexity of parsing, analyzing, and determining the best execution plan for a query increases with the more tables that are being joined together. For example, with 3 tables there's 6 ways to order the tables and JOIN them: 1. A-B-C 2. A-C-B 3. B-A-C 4. B-C-A 5. C-A-B 6. C-B-A And in Microsoft SQL Server, there are 3 types of JOIN operators it can use for any given JOIN: Nested Loops, Hash Match, Merge Join. So with only 3 tables there's actually 18 unique ways to JOIN all the data together then. That's 18 unique execution plans the optimizer needs to analyze and choose the best one from, in a very minimal amount of time. So you can imagine how quickly that number grows when you add a fourth or fifth table to JOIN to, where 15 tables is some number in the billions (maybe more?) execution plans. Because of the vast number of execution plans the optimizer needs to choose from and because the slightest change in a query in Microsoft SQL Server results in a new plan being recalculated, re-arranging the order of the tables being JOINed could result in a more optimal plan but just as well could result in a less optimal one too. Again because Microsoft SQL Server doesn't care about the order of the tables per se, rather it does the re-ordering for you under the hood, and it's essentially sheer luck at that point that you've caused a different execution plan to be generated. Though most times you'll likely see the same plan anyway. This is probably true for most database systems that optimize the JOIN order for you.