Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
246
questions with no upvoted or accepted answers
8
votes
0answers
256 views
What cases benefit from the Reduce, Replicate, and Redistribute join hints?
The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms:
Reduce
Replicate
Redistribute
However there does not seem to be much information ...
3
votes
0answers
48 views
MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN
Question
Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in ...
3
votes
0answers
157 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
0answers
198 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 ...
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
4k 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
500 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
36 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
664 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
92 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
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
0answers
479 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
111 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
320 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
128 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
82 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
954 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
332 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
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
10 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
1answer
51 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
15 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
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
41 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
36 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
35 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
80 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
94 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
189 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
108 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
91 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
29 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
57 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
190 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
117 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
2answers
73 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,
...
1
vote
0answers
64 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
vote
0answers
30 views
Getting Available Date/Times For Attendees And Avoid Conflict
+------------+ +---------------+ +--------------+
| attendees | | attendee_times| | meeting_times|
+------------+ +---------------+ +--------------+
| attendee_id| | at_id | | ...
1
vote
0answers
84 views
Do more joins equate to slower performance?
I'm working on a denormalized database dump, reconstructing the model so that we can access the data in our own system. Because the database is denormalized there is quite a bit of redundancy that I ...
