Questions tagged [join]

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

337 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4 votes
1 answer
142 views

T-SQL: Iteration vs. Set-Based Operations When Independence of Ancillary Data is Needed

BACKGROUND I am using Microsoft SQL Server Management Studio v18.9.1 on Windows 10. I've created a database to store test results of our company's widget (not yet being produced). I am working on a ...
Dave's user avatar
  • 197
3 votes
0 answers
470 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?
FbaStack's user avatar
3 votes
0 answers
7k 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 ...
A.T.'s 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 ...
postelrich's 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 ...
simpleuser008's user avatar
3 votes
0 answers
2k 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. ...
outofculture's user avatar
3 votes
1 answer
951 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 ( ...
vir's user avatar
  • 41
3 votes
1 answer
8k 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: ...
Thomas Johnson's user avatar
2 votes
2 answers
13k 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 ...
AmQ7's user avatar
  • 53
2 votes
0 answers
200 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 ...
Guiles Kompanix's user avatar
2 votes
1 answer
513 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 ...
Arthur Putnam's user avatar
2 votes
0 answers
198 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 ...
J.D.'s user avatar
  • 37.5k
2 votes
0 answers
66 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:...
Arun S R's user avatar
  • 207
2 votes
2 answers
1k views

Complex queries in CouchDB across multiple documents

I'm relatively new to CouchDB (more specifically Cloudant if it matters) and I'm having a hard time wrapping my head around something. Assume the following (simplified) document examples: { "docType"...
nute's user avatar
  • 303
2 votes
0 answers
75 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 ...
Lena Oxton's user avatar
2 votes
0 answers
43 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, `...
XMS's user avatar
  • 21
2 votes
0 answers
123 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 ...
Maksim Karatkou's user avatar
2 votes
0 answers
609 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 ...
Ramsha Khalid's user avatar
2 votes
0 answers
147 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. ...
NikxDa's user avatar
  • 121
2 votes
2 answers
93 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, ...
daniel9x's user avatar
  • 361
2 votes
0 answers
124 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 ...
MattyZDBA's user avatar
  • 1,933
2 votes
0 answers
428 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 ...
oals's user avatar
  • 121
2 votes
0 answers
167 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/...
harveyslash's user avatar
2 votes
0 answers
44 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 ...
somejkuser's user avatar
2 votes
1 answer
508 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 ...
gaganbm's user avatar
  • 141
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 ...
flukeflume's user avatar
2 votes
0 answers
399 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 ...
Jim's user avatar
  • 761
2 votes
2 answers
17k 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 ...
Matt's user avatar
  • 29
2 votes
2 answers
3k 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 ...
user3213767's user avatar
1 vote
0 answers
58 views

Column information from View

Suppose I have this database CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(255) NOT NULL, Roll INT NOT NULL, CustomID INT UNIQUE ); INSERT INTO Students (StudentID, ...
Shoaib Ahmed's user avatar
1 vote
2 answers
24 views

How do I join two tables where the result gives me multiple results for some, but I only want one

I have two tables: tng_people and tng_events in my genealogy database. tng_people stores the basic information about each person, each person having a unique personID. tng_events stores events that ...
user1088537's user avatar
1 vote
2 answers
74 views

Explain plan will sort the result after join even the column included in index

I am using SQL Server 2022 Developer Trying get all AccessLog that classified to type 1. SELECT [t].[Time], [u].[UserName], [t].[Type], [t].[Message] FROM [AccessLog] AS [t] LEFT JOIN [...
Uni's user avatar
  • 11
1 vote
0 answers
57 views

Adjust SQL query to select only one record

I have three tables: An adjacency-list table, territories with fields id, alignment_id, three_left, tree_right, depth and deleted_at (omitted from table for brevity, all values null). id alignent_id ...
ioni's user avatar
  • 61
1 vote
2 answers
102 views

How to leave only the rows with an timestamp difference with an interval greater than a parameter

I have a Postgres table with timestamps as entries. From this table, I would like to calculate a new one where there are no consecutive entries with a timestamp difference shorter than 400 ...
eddie_jung's user avatar
1 vote
1 answer
161 views

What's the fastest way to query multiple tables at once where each table represents one year of data?

I'm developing an web app based around a database with multiple decades of data with multiple schema changes over the years. Each year of data is released as a zip file containing CSVs named table1....
Tyler's user avatar
  • 111
1 vote
1 answer
106 views

How I can retrieve the latest records without using lateral join?

I have the following database: create table "client" ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, lastname VARCHAR NOT NULL ); create table store ( id SERIAL PRIMARY KEY, ...
Dimitrios Desyllas's user avatar
1 vote
1 answer
33 views

MySQL Query - Sum data according to Category Parent name - each their own column

Here is the data I have: table agencies: id code 18 agency_01 6 agency_02 table compensations id agency_id compensation_category_id amount registered_at 3 6 4 45000.00 2023-01-01 4 6 5 25000.00 ...
user268130's user avatar
1 vote
0 answers
225 views

Postgres sorted join with millions of rows, low cardinality, slow query - time to partition tables?

I have an API that is used to query for data about programs and other forms of content. We have a few tables that I need to join on to fullfil an ad-hoc query, and I am running into trouble speeding ...
flanders9er's user avatar
1 vote
0 answers
29 views

Multipile queries vs a long query with joins in my situation

I am currently working on a project where, each user can possess multipile portfolios. Each portfolio contains products, each product contains assets, which can be associated to different kinds that ...
orangetiger's user avatar
1 vote
0 answers
42 views

Increasing the 'stoptime' of generated routes

I'm learning some basic SQL skills and I was toying around with some data to see what I can do, however, I'm a bit stuck on how to proceed with my current problem. I have a table that looks like this: ...
Mathematician 42's user avatar
1 vote
1 answer
60 views

How to efficiently query with a join and a where/order across two tables?

I have two PostgreSQL tables: contents id (primary key) captured_at (date) status (enum) plenty of other columns we don't care about library_contents library_id content_id With three indexes on ...
Basile Bruneau's user avatar
1 vote
0 answers
426 views

database design - sales, products, customers, persons and organizations

I am trying to model a system where you can have persons or organizations as customers. persons or organizations can also have other sub-types like employees, suppliers and other types in the future. ...
JaySmi's user avatar
  • 21
1 vote
0 answers
209 views

Improving performance with a common low-cardinality field

I have a database with multiple tables. Every table has a field named sourcename, which indicates basically which customer the data came from. There are only about ten possible values for this field ...
kojiro's user avatar
  • 111
1 vote
0 answers
24 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 ...
Pragy Agarwal's user avatar
1 vote
0 answers
396 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,...
Harry E.'s user avatar
1 vote
0 answers
37 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 ...
PaulMcF87's user avatar
  • 167
1 vote
1 answer
613 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 ...
flyingdutchman's user avatar
1 vote
0 answers
3k views

Join JSON column using an index

I have two tables: definition (7,000 rows): +--------------+----------+ | definitionId | nameRef | +--------------+----------+ | 1 | [1] | | 2 | [1,2] | +--------------+-...
Kohjah Breese's user avatar
1 vote
1 answer
174 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` = `...
nlta's user avatar
  • 111
1 vote
1 answer
883 views

In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?

In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
Qi luo's user avatar
  • 25

1
2 3 4 5
7