Questions tagged [join]

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

258 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
166 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?
3
votes
1answer
68 views

What is better join, in or in with join

I have these tables: calls: rows [ 'created_at', 'user_id'] users: rows ['id', 'username'] And I need to get all user without calls from date: How can I get it? Or what way is better and why? I ...
3
votes
0answers
5k 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 ...
3
votes
1answer
553 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 ( ...
3
votes
1answer
6k 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: ...
2
votes
0answers
39 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:...
2
votes
0answers
67 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 ...
2
votes
0answers
33 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, `...
2
votes
0answers
1k 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 ...
2
votes
0answers
95 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
0answers
226 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
0answers
52 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. ...
2
votes
2answers
77 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, ...
2
votes
1answer
560 views

Performance: where before or after join

I am doing a query like this: select * from table1 join table2 on table1.fieldjoin = table2.fieldjoin where table1.field = 'y' I would ask is this query equivalent in terms of ...
2
votes
0answers
2k 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 ...
2
votes
0answers
114 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 ...
2
votes
0answers
329 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 ...
2
votes
0answers
138 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/...
2
votes
0answers
35 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 ...
2
votes
0answers
87 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 ...
2
votes
0answers
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. ...
2
votes
0answers
343 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 ...
2
votes
2answers
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 ...
1
vote
1answer
27 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 ...
1
vote
1answer
27 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
0answers
22 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"."...
1
vote
0answers
11 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 ...
1
vote
2answers
42 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 "...
1
vote
1answer
55 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"."...
1
vote
2answers
64 views

How do I join getting one row from the left table, no matter how many matches i get from the right table?

I have two tables - one is a data table and the other is a mapping table. I want to join them together, but only preserve the data from the left table. However, it is possible that the mapping table ...
1
vote
0answers
17 views

Understanding PostgreSQL Execution Plans

I'm trying to understand the motivation of PostgreSQL 12 query planner here. Database Structure CREATE TABLE subjects ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE, ); CREATE TABLE ...
1
vote
1answer
129 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"...
1
vote
0answers
28 views

PostgreSQL - SELECT query with ORDER BY and LIMIT 4 very slow, same query much faster with LIMIT >= 40

I've got following sample query from our Postgres DB: SELECT * FROM advertised_car a0_ LEFT JOIN car_check c1_ ON a0_.car_check_id = c1_.id WHERE c1_.status = 'ACCEPTED' ORDER BY a0_.id LIMIT 4; ...
1
vote
0answers
66 views

Is there a way to let MySQL join some external data without importing those data into DB?

I have some static data which stored in a JSON file: outinfo= [{out_id:123,out_name:'hello'}] and I have a table: ----tableA---- | id | label | out_id | | 1 | a | 123 | | 2 | b | ...
1
vote
1answer
39 views

IN subselects vs INNER JOIN (performance)

I have to make a choice: choice 1: using IN subselect choice 2: using INNER JOIN See the examples: IN subselect: SELECT contractServiceCode FROM contractServices WHERE contractID IN ( SELECT ...
1
vote
0answers
37 views

Using conditions to return specific rows

From the following table i am trying to retrieve specific rows based on a criteria +----+--------------+------+ | ID | UPDATED_DATE | TYPE | +----+--------------+------+ | 1 | 01/13 | P | |...
1
vote
0answers
81 views

Equi Join On Union- and Intersection-Compatible Relations

Suppose that I have two union- and intersection-compatible relations; i.e., R1(A,B) and R2(C,D) as: Now , I want to find the join of both relations with equality conditions A=C and B=D. I have ...
1
vote
0answers
117 views

PostgreSQL - Speeding up Index scan in left join

I have two tables: Order Lines, Orders. Orders table has a Status column, which is text. It can have values like 'Draft', 'Approved', 'Cancelled' etc. I need a query to return pending order lines for ...
1
vote
0answers
246 views

Efficiently query product variants from inventory database (avoiding EAV)

I'm building an ecommerce website for fun with three main components: frontend in Angular, MySQL database and a PHP backend that will function as a REST API. The inventory database consists of ...
1
vote
0answers
28 views

Index to facilitate joining a table against itself to gather the last observation

I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as ...
1
vote
0answers
37 views

How to optimize this Postgresql query

Table A has columns (P1, P2, Value1) where P1 and P2 are a multi column primary key. Table B has columns (P3, P1, Q1) where P3 is the primary key. Table C has columns (P2, Q1, Value2) where P2 and Q1 ...
1
vote
0answers
40 views

Finding the different rows from two resultsets based on one of the resultsets (MINUS, NOT EXISTS)

I'm testing queries on two resultsets that have the same value in certain columns on Oracle 11g. I want to exclude the rows that share these columns from my final result based on the first table. Say ...
1
vote
0answers
131 views

MS Access 2016 M:N relationships return multiple columns instead of multiple rows from a JOIN

Let's assume a data model like this: Books could have multiple authors as well as authors could have written multiple books. If a book and its authors are selected by the following statement (this ...
1
vote
0answers
135 views

Avoid adding an id-only table for a one-to-many table

I have a table that links user IDs with external identities (one-to-many) and I would like to avoid creating a second table with just IDs. Simplified example: CREATE TABLE identities ( uid ...
1
vote
0answers
31 views

How to make use of indexes with inner join in Postgis?

I got 2 sets of points in 2 separate tables. Table_a got 100k points and table_b got 300k of points. I trying to find nearest points in relation find me any point from table_b that is within 50 meters ...
1
vote
1answer
60 views

How to get INNER JOIN result from two tables

I have the three MySql tables : table_fonts font_id (int) font_name (varchar) table_charsets charset_id (int) charset_name (varchar) (examples: 1=latin, 2=greek, and so on) table_charsets_by_font ...
1
vote
2answers
198 views

MYSQL Update data based on top 10 records of each user

I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
1
vote
0answers
122 views

One to Many avoid redundancy MySql

I need to store products in two forms, simple and variable. Simple products have a name, desc and price. Variable products have a name and description and each variation will have additional fields, ...
1
vote
0answers
65 views

Analyzing and optimizing multi join query?

I'm working on a project to familiarize myself with more complex sql, I have limited db experience. I'm building a reporting application, and each report will have a series of questions and category ...
1
vote
0answers
1k views

Joining recursive table in PostgreSQL

Situation I have two simple tables, one called "node_relations" and one called "node_information". The table "node_relations" represents a simple parent-child hierarchy, having only two columns "id" ...

1
2 3 4 5 6