Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
323
questions with no upvoted or accepted answers
3
votes
0
answers
272
views
Block nested loop join cost
What is the cost of block nested loop join?
Given B1 blocks of R1 (outer relation) and B2 blocks of R2 (inner relation), why does it cost B1+B1*B2?
- 31
3
votes
0
answers
4k
views
Huge speed difference between left join and inner join - Postgres
It is known that left join is generally slower than inner join, but this difference seems out of proportion.
I have table A with 2,542,526 rows and table B with 30,444 rows.
I ran this query, which ...
- 131
3
votes
0
answers
3k
views
Slow query caused by nested loop on simple join?
I've been struggling with the performance on a query. Trying to do a lookup of a large set of ids in a large table (500GB). I create a temporary table of the ids with the ids as a primary key and run ...
- 195
3
votes
0
answers
6k
views
Optimize long LEFT OUTER JOIN query
I have query which has 40+ left outer joins. Query is not that slow but I'm interested that can I optimize this (procedure is most used one in db). I had idea that "a" table has type column which ...
- 303
3
votes
1
answer
739
views
Query with conditional JOIN running slow
I have a query the first part of which looks like this -
With CTE AS (
select DISTINCT
A.col1,
A.col2,
.
.
.
from tableA A
JOIN tableB B ON
(
...
- 41
3
votes
1
answer
7k
views
MySQL: NOT EXISTS vs LEFT OUTER JOIN...IS NULL
Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better:
...
- 451
2
votes
0
answers
95
views
Why does SQL Server 2019 stored proc get Adaptive Join on Development Server, but not on Production Server?
I have a stored proc with one select statement that gets a plan w/ Adaptive Joins on my development server, but not on my production server. What could explain that? I'm not much interested in ...
2
votes
0
answers
148
views
How to determine the Tipping Point of which Physical Join Operator gets used to process a query?
I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
- 25.6k
2
votes
0
answers
52
views
Why can't outer join simplification happen for embedding outer join?
The MySQL documentation here suggests the following:
Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:...
- 165
2
votes
0
answers
74
views
What's the more efficient way of selecting with multi left joins?
I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities.
All tables have a id column as the primary key. student.id is varchar(64) (similar ...
- 21
2
votes
0
answers
42
views
Need to update a table with an iterative number based on a subquery
Massive edit to follow procedure and enable reproducing this in a fiddler somewhere.
Table 1, schedule:
CREATE TABLE `schedule` (
`scheduleid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`...
- 21
2
votes
0
answers
112
views
Postgres / slow query / order by
I'm joining two tables, filter out results by second table and order results by first table's ID.
The query is super-slow on Postgres Database. I see the issue is in the ORDER BY statement. But ...
2
votes
0
answers
448
views
Query for getting reserved seats in a cinema
I have this cinema project for a course.
And I have to visually show the seats to user so they can reserve their preferred seats.
I need to get the reserved seats from the database of that particular ...
2
votes
0
answers
76
views
MariaDB running indefinitely on inner join with count
I am currently experiencing a very, very weird issue with a query in MariaDB. Our server experiences a "504 Gateway Timeout" quite often after a database upgrade, and I was investigating the issue.
...
- 121
2
votes
2
answers
88
views
Given the following tables/index/explain planning, why is the query so slow?
I have the following tables:
CREATE TABLE trace
(
uuid uuid NOT NULL,
result text,
ruleid uuid,
previousruleresultid uuid,
senderduns text,
receiverduns text,
eventid uuid,
...
- 321
2
votes
0
answers
120
views
Do Large Hash Joins Contribute to Stolen Pages?
On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key.
100 GB is the max server memory setting on a 128 ...
- 1,913
2
votes
0
answers
369
views
How does the use of derived tables speed up sorting?
In the article 45 Ways To Speed Up Firebird Database, it states that
Use derived tables to optimize SELECT with ORDER BY/GROUP BY
Another way to optimize SQL query with sorting is to use ...
- 121
2
votes
0
answers
160
views
Groupwise max then join
I have a votes table that has many subitems. I have a subitems action table that records what action was performed by which user on which group, on which subitem, on which vote
Users can either cast/...
- 305
2
votes
1
answer
8k
views
Combining GROUP BY and JOIN
This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
- 29
2
votes
0
answers
39
views
Join integration query involving multiple associations
I'd like some assistance in fixing this query. Let me explain the background on what it currently does, and then what I need to integrate.
First, here are my current tables.
users (performers) table
...
- 727
2
votes
0
answers
152
views
Delete using join or using sub-query?
I need to delete rows from a table based on what is present in a temporary table.
For me, both of these statements work :
DELETE from main_table where `id` in (select `deletable_id` from ...
- 141
2
votes
0
answers
1k
views
How to improve performance when joining against coalesce
In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. ...
- 121
2
votes
0
answers
2k
views
Performance hits with MySQL Left Joins and Order By
There are a lot of questions out there regarding this issue, but most answers seem to be very case-specific. I've tried to make this general so it'll help others too.
The Issue
We have a smallish ...
- 121
2
votes
0
answers
372
views
How to index for a group by with columns from different tables?
Is it possible to create an index from columns of 2 different tables?
If not, how do we optimize a query that does a group by on columns of 2 different tables?
For example: the following ...
- 741
2
votes
2
answers
2k
views
Interpretation of multiple join order
I have a problem using multiple joins in one SQL Statement. For example, I have 10 Tables which are tbl1, tbl2, tbl3,..,tbl10. I would like to select some columns from each table.
If I use joins in ...
- 135
1
vote
0
answers
16
views
Best way to create a View which includes counts for multiple one-many relationships?
Consider three entities - Forum, Thread & Topic, with the following relationships
Forum has many Threads
Forum has many Topics
I want to create a View forum_details that displays all columns of ...
- 185
1
vote
0
answers
52
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
1
vote
0
answers
24
views
Getting totals from different tables to create overall total
I currently have 2 tables TableA and TableB` both within the same MYSQL Database.
I am trying to find a way to take totals from both tables and add them together to give a definitive total.
The tables ...
- 111
1
vote
1
answer
84
views
SELECT only IS NOT NULL columns with LEFT JOIN
I have a query where i need to select product options from multi tables.
SELECT
p.option_id,
p.product_id,
p.sku,
p.qty,
p.price,
c.color_label,
s.size_label,
m.material_label,
v.variation_label
...
- 43
1
vote
1
answer
115
views
Simple query with a single join very slow
I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
1
vote
0
answers
129
views
Join JSON column using an index
I have two tables:
definition (7,000 rows):
+--------------+----------+
| definitionId | nameRef |
+--------------+----------+
| 1 | [1] |
| 2 | [1,2] |
+--------------+-...
- 155
1
vote
1
answer
44
views
Best practices for large JOINs - Warehouse or External Compute (e.g. Spark)
I am working on a problem that requires a very large join. The JOIN itself is pretty straightforward but the amount of data I am processing is very large. I am wondering for very large JOINs, is there ...
- 391
1
vote
1
answer
83
views
How to index a query with many joins
I have this query with this schema that takes ~8s on my laptop and longer on the vm running the service.
SELECT DISTINCT `mofs`.*
FROM `mofs`
INNER JOIN `isotherms` ON `isotherms`.`mof_id` = `...
- 111
1
vote
0
answers
149
views
Filling gaps in result with empty data
PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Hello All,
I'm a bit stuck on this one.
Working SQL
Currently I am using this query:
...
- 43
1
vote
1
answer
127
views
PostgreSQL Query Optimization - Joining with a lookup table does not use index
I am attempting to optimize indexes and querying for a postgres database, and have run into some issues around a lookup table I have used to support joins for a many:many relationship. Here is the ...
- 11
1
vote
0
answers
45
views
JOINing two mapping tables
I have two mapping tables as
CREATE TABLE ProjectDevelopers
(
DeveloperID int(11) unsigned NOT NULL,
ProjectID int(11) unsigned NOT NULL,
INDEX(ProjectID),
PRIMARY KEY(DeveloperID,ProjectID)
) ENGINE=...
- 4,261
1
vote
0
answers
30
views
List Player Record Types, First Score, and Last Score
I have a record_types and a record table:
CREATE TABLE record_types(
id SERIAL PRIMARY KEY,
record_type VARCHAR(50)
);
INSERT INTO record_types(record_type) VALUES ('dribble');
INSERT INTO ...
- 119
1
vote
1
answer
107
views
PostgreSQL chooses a wrong order of Hash Join
EXPLAIN shows that PostgreSQL chooses a bad plan for hash join. it should have first iterate by tags_name_value_height2 in the table tags and use it to find corresponding transactions.id, but instead ...
- 725
1
vote
2
answers
49
views
Under what circumstances would these 3 queries return different number of results?
Hello I have the following question:
Query 1:
SELECT b.id,
(
SELECT SUM(cb.invoice_amount) AS sum
FROM payment_lines cb
WHERE cb.batch_id = b.id
) AS batch_total_invoice_amount
...
- 19
1
vote
1
answer
63
views
Getting the latest timestamp in Oracle
I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in ...
- 11
1
vote
2
answers
955
views
MySQL: Data pagination according data on another table
I have a database with the following structure
Items table: itemID, itemName
Ads table: AdsId, itemID
I would like to make pagination on items table, 10 items in each page, but first I must retrieve ...
- 113
1
vote
1
answer
22
views
sqlite 3.4 updating table with data from different table - changed with 3.3 - newbie question
All of the other questions on this seem to be before 3.3 when sqlite added an update join feature? Newbie here, so I may even be getting the terminology wrong. I am trying to update the issbn field in ...
- 11
1
vote
0
answers
269
views
SQL: ON vs. WHERE in SEMI JOIN
Considering the following two (equivalent) SQL statements.
Why do their execution plan differ (especially in performance) that much?
I'm using Postgres 11.9 in this example.
select p.name
from person ...
- 111
1
vote
0
answers
85
views
Preventing three SQL Left Joins from creating duplicate rows
I have 4 tables [tA] is the main data point. A customer could have more than one ID Code, or the same ID Code which has been renewed, and ID Codes can be transferred between customers, but a customer ...
- 11
1
vote
1
answer
61
views
Count nested joined table records only if the table associated via bridge table have two records with given names
I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert ...
- 149
1
vote
1
answer
38
views
optimizing Postgresql query with left join and indexes
I am trying to run the following query but it is taking forever to execute:
Select Distinct ON (S1.sql_id)
S1.msgdata as replymg,
S1.time as replytme,
S2.receiver,
S2.time,...
1
vote
0
answers
25
views
How to return most recent element with distinct subquery
I have such query but it returns duplicated rows for same speaker
SELECT
"speakers"."id",
"speakers"."name",
"speakers"."surname",
"speakers"."photo_url" as "photoURL",
"talks"."...
- 123
1
vote
0
answers
26
views
Calculating forecast data in parallel
I'd like to ask about how to calculate forecast data in parallel in a relational DB like PostgreSQL, which seems like a very typical problem when doing forecasts. Let's say we're trying to get the ...
- 111
1
vote
2
answers
58
views
What should I index when joining on a table with conditions?
I have a query like this:
SELECT "main_table".*
FROM "main_table"
INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL
AND "other_table"."id" = "main_table"."appointment_id"
WHERE "...
- 545
1
vote
1
answer
75
views
Postgresql group by for many columns
I have such query:
SELECT "Movie"."title",
"Movie"."id",
"Movie"."publicationDate",
"Movie"."authorID",
"Author"."photoURL" as "authorPhoto",
concat("Author"."...
- 123