New answers tagged

6

Expanding on SMor's comment: The short answer is no - it does not matter whether you put filters into the join or the where clause when you use INNER JOINs. Use outer joins changes the situation greatly. And as usual, there are no absolute answers to any performance question. If 2 queries are logically the same, you need to examine the execution plans to ...


-1

was faced with a similar predicament using SQL 2008R2. The Main transaction table has three columns that are related to another table Notes. My solution was to create a subquery for the columns of concern and outer join to the main table. Tbl1 has three columns GId, VId and BId which relate to the primary key FieldID in Tbl2. The value we want for the report ...


13

Pretty much any "does it matter if I write something x way or y way" question can only be answered "maybe". Optimizers have to consider a lot of potential join paths and query plans and they don't have a lot of time to produce a plan. That means they end up using a lot of heuristics to prune potential plan trees early rather than fully ...


0

you can use a GROUP BY to get rid of duplicate email and max(lastupdate) the get the last update. SELECT email, max(lastupdated) FROM `FantasyTeams` A LEFT JOIN `Users` B ON (A.handle = B.handle) Where lastupdated < '2021-08-31' AND lastupdated > '2021-04-31' AND email IS NOT NULL group by email ORDER BY 2 DESC


0

I would make a separate subquery for the Users table, and deal with duplicates there: SELECT a.email,a.lastupdated FROM FantasyTeams A LEFT JOIN ( SELECT DISTINCT email,lastupdated,handle FROM Users WHERE lastupdated <'2021-08-31' AND lastupdated>'2021-04-31' AND email is not null ) B ON A.handle = B.handle ORDER BY lastupdated DESC If you have ...


2

Here is an optimized, bug-fixed query. Fiddle for demonstration. WITH cte1 AS ( SELECT *, (X-2) AS X_START, (X+2) AS X_END, (Y-2) AS Y_START, (Y+2) AS Y_END, (Z*1.2) AS Z_MAX, DENSE_RANK() OVER (PARTITION BY ID1, ID2 ORDER BY ACT_TIME) AS `DENSE_RANK` FROM data ), cte3 AS ( SELECT c.ID FROM cte1 JOIN cte1 c ON (cte1.ID <> c.ID and cte1....


0

I don't think you need CTEs. See if something like this will work: SELECT d1.*, d2.* FROM data d1 JOIN data d2 ON d2.x BETWEEN d1.x - 2 AND d1.x + 2 AND d2.y BETWEEN d1.y - 2 AND d1.y + 2 AND d2.z ... (I don't understand the criteria here) AND d2.act_time < d1.act_time Indexes for the optimizer ...


2

A better (design) solution would be to have another table named matchplayer. This simplistic table would consist of the two columns match_id and player_id. You then JOIN on the match_id and then on the player_id. SELECT m.id, m.date, p.name from FROM Match as m JOIN matchplayer as mp ON mp.match_id = m.id JOIN Player as p ON mp....


0

You can use an EXISTS subquery "SELECT Table0.M FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID_V INNER JOIN LM ON Table2.ID_M = Table0.ID_M WHERE Table1.IDX_V = @IDX_V AND EXISTS (SELECT 1 FROM Table3 WHERE Table2.ID_C = Table3.ID_C AND Table3.ID_C = @ID_C);" Note the use of proper parameters, rather than injection. And the ...


2

If the row is 100% duplicate, you could try to use distinct. This will give you unique results. This would not be "skipping" a row but would only show 1 row for any duplicates. "SELECT DISTINCT Table0.M " + "FROM Table1 INNER JOIN " + "Table2 ON Table1.ID = Table2.ID_V INNER JOIN " + "LM ON Table2.ID_M = ...


1

You can use outer apply. Note, however, that in an apply, the entire inner select is logically evaluated per row. So the results may be different from what you'd expect from a join, where rows are matched based on the on clause. (Obviously the compiler may choose other strategies, the effect will be the same.) There is no on clause, instead use a where ...


4

You must join 2 copies of players table. SELECT m.id, p1.name player_1, p2.name player_2 FROM matches m JOIN players p1 ON m.player_1 = p1.id JOIN players p2 ON m.player_2 = p2.id


1

Technically its an unimplemented (so far) full outer join but you can fake it with: SELECT t2.id AS id, col1, col2 FROM t1 LEFT JOIN t2 USING id UNION ALL (SELECT t1.id, col1, col2 FROM t2 LEFT JOIN t1 USING id WHERE col1 IS NULL )


2

Uniqueness over a lot of tables. This can be solved by additional table + triggers. Like CREATE TABLE tableABC (id VARCHAR(255) UNIQUE); CREATE TRIGGER tr_bi_a BEFORE INSERT ON tableA FOR EACH ROW INSERT INTO tableABC VALUES (NEW.id); DEMO If this column value can be altered then you must create according BEFORE UPDATE triggers (which removes OLD.id and ...


3

Postgres 14 adds SQL syntax addressing this problem. Now you can append an AS clause to declare a new table alias for the columns merged in the USING list: CREATE OR REPLACE FUNCTION f_merge_foobar() RETURNS TABLE(ts int, foo text, bar text) LANGUAGE plpgsql AS $func$ BEGIN FOR ts, foo, bar IN SELECT merged.ts, f.foo, b.bar FROM foo f ...


Top 50 recent answers are included