Questions tagged [join]

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

218 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8
votes
0answers
140 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
62 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
96 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
61 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
3k 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
359 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
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
30 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
135 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
70 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 issue is in ORDER BY statement. But increasing ...
2
votes
0answers
48 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
398 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
104 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
296 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
1answer
107 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
2
votes
0answers
110 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
33 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
73 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
875 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
313 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

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
28 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
34 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
61 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
66 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
125 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
35 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
41 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
40 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
27 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
2k views

How to get count from 2 sql tables with conditions on count

I have 2 sql tables as below only one column is common to both I need to get the count of all ID_Tag from Table 1 which appear in Table 2. Result condition List all ID_Tag with Case No from table ...
1
vote
1answer
52 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
127 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
100 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
70 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
2answers
974 views

Performance of a big INSERT…SELECT with multiple JOIN

I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production ...
1
vote
0answers
61 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
28 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 ...
1
vote
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 ...
1
vote
0answers
35 views

Dynamically determine the starting time and ending time for a. . . Breakfast of Champions!

Dynamically determine the starting time and ending time for a between statement based on the results of a previous query's table records when ordered appropriately for TOP and BOTTOM records ...
1
vote
0answers
164 views

Simple join unusably slow on RDS Postgres

I just setup a new AWS RDS postgres server and queries are painfully slow (> 10 hours) such that most of the time I just end up killing the query. I have done some indexing that cut the EXPLAIN cost ...
1
vote
0answers
48 views

SQL Join Query to missing Some records from first table

I am creating a database application in SQL Server. I have 2 tables named tblClient (Client Table) and tblExpEntry (Expense Table). I want to select all Clients from tblClient table and show their ...
1
vote
1answer
2k views

Index is not used when joining two tables in a stored procedure

In MySQL 5.7, I have two tables: CREATE TABLE `periodholdings ` ( `stkcode` varchar(255) NOT NULL, `participantid` varchar(255) DEFAULT NULL, `participantname` varchar(255) DEFAULT NULL, `...