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 ...
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 ...
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
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
...
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 ...
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....
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....
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
select a.*,
b.Start_Date,
b.End_date,
sum(b.cost) as total_Cost
from Ess.TZeewrld a
left join Ess.Wrldest b on a.account_number, = b.account_number,
group by b.Start_Date, b.End_date;
For starters, assuming we're talking about Oracle (given the reference to TOAD), the group by clause must explicitly include every non-...
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 ...
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
)
Only top voted, non community-wiki answers of a minimum length are eligible