Questions tagged [join]

An SQL join clause combines records from two or more tables or views.

323 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
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?
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ( ...
user avatar
  • 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: ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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:...
user avatar
  • 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 ...
user avatar
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, `...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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. ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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/...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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. ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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,...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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] | +--------------+-...
user avatar
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 ...
user avatar
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` = `...
user avatar
  • 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: ...
user avatar
  • 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 ...
user avatar
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=...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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,...
user avatar
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"."...
user avatar
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 ...
user avatar
  • 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 "...
user avatar
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"."...
user avatar

1
2 3 4 5
7