Hot answers tagged

137

If I understand you correctly, you are looking for a filtered (conditional) aggregate: SELECT a.agent_id as agent_id, COUNT(a.id) filter (where disposition = 'Completed Survey') as CompletedSurvey, count(a.id) filter (where disposition = 'Partial Survey') as partial_survey FROM forms a WHERE a.created_at >= '2015-08-01' AND a....


104

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


88

What would address your question is the subject JOIN DECOMPOSITION. According to Page 209 of the Book You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query: SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN post ...


69

The biggest difference is not in the join vs not exists, it is (as written), the SELECT *. On the first example, you get all columns from both A and B, whereas in the second example, you get only columns from A. In SQL Server, the second variant is slightly faster in a very simple contrived example: Create two sample tables: CREATE TABLE dbo.A ( A_ID ...


49

Can anyone tell how exactly apply works and how will it effect the performance in very large data APPLY is a correlated join (called a LATERAL JOIN in some products and newer versions of the SQL Standard). Like any logical construction, it has no direct impact on performance. In principle, we should be able to write a query using any logically equivalent ...


39

In Postgres (and probably any RDBMS to a similar extent, MySQL to a lesser extent), fewer queries are almost always much faster. The overhead of parsing and planning multiple queries is already more than any possible gain in most cases. Not to speak of additional work to be done in the client, combining the results, which is typically much slower at that. ...


35

There are quite a few ways to achieve your desired results. Undeterministic methods (in the event that many rows in table 2 match one in table 1) UPDATE T1 SET address = T2.address, phone2 = T2.phone FROM #Table1 T1 JOIN #Table2 T2 ON T1.gender = T2.gender AND T1.birthdate = T2.birthdate Or a slightly more concise ...


32

The performance will be the same. The optimizer will recognize this and create the same plan. On the other hand I wouldn't say they are equal. The first form in the question is far more readable and generally expected. For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query. You ...


31

SELECT a.a1,b.b1, CASE WHEN b.b1 is NULL THEN 5 ELSE b.b2 END AS b2 FROM a LEFT OUTER JOIN b ON a.a1 = b.b1


31

Logically they are identical, but NOT EXISTS is closer to the AntiSemiJoin that you're asking for, and is generally preferred. It also highlights better that you can't access the columns in B, because it's only used as a filter (as opposed to having them available with NULL values). Many years ago (SQL Server 6.0 ish), LEFT JOIN was quicker, but that hasn't ...


31

Why can't a null be equal to a null for the sake of a join? Just tell Oracle to do that: select * from one t1 join two t2 on coalesce(t1.id, -1) = coalesce(t2.id, -1); (Note that in standard SQL you could use t1.id is not distinct from t2.id to get a null-safe equality operator, but Oracle does not support that) But this will only work if the ...


30

Is this intentional? It is by design, yes. The best public source for this assertion was unfortunately lost when Microsoft retired the Connect feedback site, obliterating many useful comments from developers on the SQL Server team. Anyway, the current optimizer design does not actively seek to avoid unnecessary sorts per se. This is most often encountered ...


27

Quoting the manual: There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the USING clause. Which technique is more appropriate depends on the specific circumstances. Bold emphasis mine. Using information that is not contained in another table ...


26

This would be more efficient: With jsonb and jsonb_array_elements_text() in pg 9.4+ EXPLAIN SELECT p.id AS p_id, p.data , c.id AS c_id, c.data FROM test p LEFT JOIN LATERAL jsonb_array_elements_text(p.data->'children') pc(child) ON TRUE LEFT JOIN test c ON c.id = pc.child::int; db<>fiddle here About jsonb_array_elements_text(): How to turn ...


25

The proper form would be (assuming current pg version 9.3 for lack of information): UPDATE C SET column1 = A.column1 , column2 = B.column2 , column3 = A.column1 + B.column2 FROM A JOIN B ON A.id = B.id -- ??? not specified in question! WHERE C.id = A.id -- ??? not specified in question! AND (C.column1, C.column2, C.column3) IS ...


24

The reason your query did not work as intended: Inner join gives you the intersection of 2 tables. In your case, there was no entry for 5th street in your users table and that is why join did not produce any entry for that. Outer join (right or left) will give the result of inner join and in addition all non-qualifying records from the left or right table ...


23

You've got a bunch of different questions in here, so let's break 'em out individually. Q: If I join two tables in the same database with the above query, why is it slow? A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a ...


22

They are semantically identical and the optimiser should have no trouble recognising this fact and generating identical plans. I tend to put conditions referencing both tables in the ON and conditions referencing just one table in the WHERE. For OUTER JOINS moving the conditions can affect the semantics however.


21

There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it. In your case, you probably do not have employees with no work_log (no rows in that table), so LEFT JOIN and JOIN will be equivalent in results. However, if you had such a thing (a new employee with no registered work_log), a ...


21

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or at the start of the function definition, declaring how you want such conflicts to be resolved. The 3 possible settings are error (the default), use_variable and use_column: CREATE OR REPLACE FUNCTION ...


21

What's happening here is the Nested Loop is way off on one side. Nested Loops work really well when one side is very small, such as returning one row. In your query, the planner fumbles here and estimates that a Hash Join will return just one row. Instead, that Hash Join (property_id = id) returns 1,338 rows. This forces 1,338 loops to run on the other side ...


20

I'm pretty certain the table definitions are close to this: CREATE TABLE dbo.households ( tempId integer NOT NULL, n integer NOT NULL, HHID integer IDENTITY NOT NULL, CONSTRAINT [UQ dbo.households HHID] UNIQUE NONCLUSTERED (HHID), CONSTRAINT [PK dbo.households tempId, n] PRIMARY KEY CLUSTERED (tempId, n) ); ...


20

Joining tables is a fundamental principle of relational databases. In your case, A and B are related with the id column, which means that you can use a syntax similar to this one: SELECT a.id, a.name, a.num, b.date, b.roll FROM a INNER JOIN b ON a.id=b.id; INNER JOIN means that you'll only see rows where there are matching records in A and B. If you want ...


20

Can I eliminate the sort without changing the query (which is vendor code, so I'd really rather not...). I can change the table and indexes. If you can change the indexes, then changing the order of the index on #right to match the order of the filters in the join removes the sort (for me): CREATE CLUSTERED INDEX IX ON #right (c, a, b, d, e, f, g, h) ...


20

GLFiscalYearPeriods is a table and this comma implies a cross join. (Cartesian product) It seems that the query returns some values for each Fiscal Year. Given this tables: create table a (id int, foo int); create table c (id int); insert into a values (1,1),(2,2),(3,3); insert into c values (10),(20); select * from a, c; select * from a cross join c; ...


18

What's happening here is the subquery is looking at each individual salary and essentially ranking them, then comparing those salaries to the outer salary (a separate query against the same table). So in this case if you said N = 4 it is saying: WHERE 3 = (number of salaries > outer salary) So looking at the data you have, let's rank them in order, and ...


18

Logically, it makes no difference at all whether you place conditions in the join clause of an INNER JOIN or the WHERE clause of the same SELECT. The effect is the same. (Not the case for OUTER JOIN!) While operating with default settings it also makes no difference for the query plan or performance. Postgres is free to rearrange joins and JOIN & WHERE ...


17

Hierarchical queries, as those recursive queries are known, are now supported in MySQL 8. Old Answer Alternatively, you can find a dynamic (and thus, potentially dangerous) trick here: https://stackoverflow.com/questions/8104187/mysql-hierarchical-queries You can also find a discussion on how to store hierarchical data with other models than with an ...


16

Your existing query is close to something that you could use but you can get the result easily by making a few changes. By altering your query to use the APPLY operator and implementing CROSS APPLY. This will return the row that meets your requirements. Here's a version that you could use: SELECT u.Username, u.UserType, u.Points, lv.Level FROM ...


16

The documentation is a little misleading. The DMV is a non-materialized view, and does not have a primary key as such. The underlying definitions are a little complex but a simplified definition of sys.query_store_plan is: CREATE VIEW sys.query_store_plan AS SELECT PPM.plan_id -- various other attributes FROM sys.plan_persist_plan_merged AS PPM LEFT ...


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