Questions tagged [join]

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

Filter by
Sorted by
Tagged with
18 votes
2 answers
5k views

What does [FROM x, y] mean in Postgres?

I'm just getting started with Postgres. Reading this document I came across this query: SELECT title, ts_rank_cd(textsearch, query) AS rank FROM apod, to_tsquery('neutrino|(dark & matter)') query ...
user avatar
45 votes
7 answers
87k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
user avatar
2 votes
1 answer
1k views

mysql directory grow to 246G after one query, which failed due to table is full

I was trying to run the following statement with the hope to create a join of two existing tables. create table CRS_PAIR select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, ...
user avatar
  • 123
98 votes
4 answers
230k views

Best practice between using LEFT JOIN or NOT EXISTS

Is there a best practice between using a LEFT JOIN or a NOT EXISTS format? What is benefit to using one over the other? If none, which should be preferred? SELECT * FROM tableA A LEFT JOIN tableB B ...
user avatar
16 votes
4 answers
2k views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 t1, ...
user avatar
  • 263
4 votes
2 answers
12k views

Select rows where value of second column is not present in first column

I have this table structure: id1 id2 1 2 1 3 1 4 2 1 2 5 I need to build a query to select id2 where id1 is not in id2. For example if id1 = 1 then only id2=3 and id2=4. I tried ...
user avatar
  • 1,690
6 votes
1 answer
4k views

What is the recommended way to join junction tables for efficient ordering/pagination?

Summary: I have a simple database schema but even with just a few 10's of thousands of records the performance on basic queries is already becoming a problem. Database: PostgreSQL 9.6 Simplified ...
user avatar
15 votes
2 answers
29k views

Postgres JOIN conditions vs WHERE conditions

Postgres newbie here. I'm wondering if this query is optimized or not? I tried to JOIN ON only the values that are 100% necessary and leaving all the dynamic conditions in the WHERE clause. See ...
user avatar
  • 347
5 votes
2 answers
14k views

Understanding multiple table join with aggregation

I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2 CREATE TABLE main ( id SERIAL PRIMARY KEY, name text NOT NULL ); ...
user avatar
  • 1,049
16 votes
4 answers
57k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
user avatar
  • 163
19 votes
2 answers
7k views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
user avatar
7 votes
2 answers
24k views

MySQL: LEFT JOIN not working as expected

I have 2 tables: Employees, attendanceIn When I query using LEFT JOIN SELECT employees.eno,employees.name,employees.dept,attendanceIn.attIn FROM `employees` LEFT JOIN attendanceIn ON employees.eno ...
user avatar
  • 173
3 votes
2 answers
894 views

get column from too many tables in mysql

.I have too many tables X1,X2,...Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company. every table has structure something like this desc X1; +---------+------...
user avatar
  • 473
0 votes
3 answers
6k views

Update values in one table based on contents of another

Software Name: Oracle SQL Developer Version 17.2.0.188 I have three tables: orders, order_details, and products. I wanted to run a query to update units_in_stock in the products table based on ...
user avatar
  • 19
47 votes
4 answers
81k views

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? TL;...
user avatar
  • 2,411
29 votes
2 answers
59k views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES ('{"parent":null,"children":[2,3]}'), ('{"parent":1, "children":[4,5]}'), ('{"parent":...
user avatar
  • 1,231
10 votes
2 answers
97k views

Joining multiple tables results in duplicate rows

I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. There are multiple tables that have different information in them. Person ...
user avatar
8 votes
1 answer
14k views

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName ...
user avatar
2 votes
1 answer
3k views

Are implicit joins as efficient as explicit joins in Postgres?

It's often convenient to write: SELECT * FROM t1 # ... +many more tables INNER JOIN t2 ON (t1.id = t2.col) INNER JOIN t3 ON (t1.id = t3.col) INNER JOIN t4 ON (t1.id = t4.col) ... as a cross join ...
user avatar
  • 161
12 votes
3 answers
4k views

Fastest way to count how many dates' ranges cover each date from series

I have a table (in PostgreSQL 9.4) that looks like this: CREATE TABLE dates_ranges (kind int, start_date date, end_date date); INSERT INTO dates_ranges VALUES (1, '2018-01-01', '2018-01-31'), ...
user avatar
  • 231
3 votes
1 answer
2k views

Fill missing dates within groups

I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date ...
user avatar
  • 187
5 votes
1 answer
3k views

How does mysql deal with queries that touches myisam and innodb tables?

In our mysql database, we use both myisam and innodb tables, though there are more myisam tables. QUESTIONS If a query involves both myisam and innodb tables, will the query use table lock or row ...
user avatar
  • 601
5 votes
1 answer
2k views

Why does this implicit join get planned differently than an explicit join?

In this answer I explain SQL-89s implicit syntax. But I noticed different query plans while playing around: EXPLAIN ANALYZE SELECT * FROM (values(1)) AS t(x), (values(2)) AS g(y); ...
user avatar
  • 56.7k
4 votes
3 answers
704 views

SQL JOIN Syntax in MS SQL

I have been taught in my MSSQL classes, this is how to join two tables select * from FirstTable A JOIN SecondTable B on A.ID= B.ID Now in my professional life, I came across JOIN queries like this ...
user avatar
  • 773
1 vote
1 answer
670 views

How does MariaDB handle joins on ENUMs?

I'm just wondering because MySQL ENUMs are essentially tied to the table, how do they handle JOINs if two tables have the same ENUM? Are they join based on the ENUM value's or the string-key (input)? ...
user avatar
  • 56.7k
14 votes
2 answers
104k views

Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and I want to update the ...
user avatar
15 votes
1 answer
35k views

SUM over distinct rows with multiple joins

Schema: CREATE TABLE "items" ( "id" SERIAL NOT NULL PRIMARY KEY, "country" VARCHAR(2) NOT NULL, "created" TIMESTAMP WITH TIME ZONE NOT ...
user avatar
23 votes
3 answers
126k views

What is more efficient, a where clause or a join with million plus row tables?

We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows. Sample structures: MasterTable (Id, UserId, Created, Updated...) -...
user avatar
12 votes
1 answer
3k views

Combine two event tables into a single timeline

Given two tables: CREATE TABLE foo (ts timestamp, foo text); CREATE TABLE bar (ts timestamp, bar text); I wish to write a query that returns values for ts, foo, and bar that represents a unified ...
user avatar
26 votes
6 answers
3k views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
user avatar
6 votes
7 answers
16k views

How to find the nth highest salary in SQL?

I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)? EmpID Salary 1 90000 2 80000 3 54000 4 ...
user avatar
1 vote
1 answer
919 views

For each field name in a list of fields, get the unique values

The Question: I have a view that is a list of fields in tables: FIELD_LIST_VW +-------------+------------+ | TABLE_NAME | FIELD_NAME | +-------------+------------+ | ENG.TABLE_1 | FIELD_1 | | ...
user avatar
  • 1,387
1 vote
1 answer
5k views

Full text search on multiple joined tables

I'm trying to set up a full text search in PostgreSQL but coming across the problem that the more tables I join to the query the fewer the results I get back. The tables I am trying to join are: ...
user avatar
5 votes
1 answer
274 views

Sort by number of related rows in referencing table

Let there be two tables: Users id [pk] | name --------+--------- 1 | Alice 2 | Bob 3 | Charlie 4 | Dan Emails id | user_id | email ----+---------+------- 1 | 1 |...
user avatar
3 votes
3 answers
1k views

Full Outer Join shows NULL when Group By is used

I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt is to have each subquery as a column all grouped by the 'Group Name'. Here is the query: select coalesce(co....
user avatar
  • 31
2 votes
1 answer
669 views

Full outer joins, coalescing, indexes and views

I have two tables, let's call them TableA and TableB. TableA TableB ------ ------ ID ID Date Date Foo Bar Both tables ...
user avatar
  • 2,833
1 vote
2 answers
16k views

How to run update query on multiple table via joins simultaneously on postgres?

In MySQL, I can do a query like this UPDATE prd_sectionshapename se inner join prd_shape s ON s.id = se.shape_id SET se.company_shape_name = 'ABC', s.name_en = 'Another name' ...
user avatar
1 vote
1 answer
152 views

Generate multiple running totals with GROUP BY day

I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running ...
user avatar
0 votes
1 answer
1k views

How to query Last inserted row for each group in oracle 10

I have two tables about users where I want to join two tables and show the latest remarks of each user bellow is table I want to query the latest remarks for each user (1 remark per user) bellow ...
user avatar
102 votes
3 answers
146k views

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, ...
user avatar
  • 1,121
42 votes
2 answers
97k views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
user avatar
23 votes
2 answers
40k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
user avatar
39 votes
1 answer
18k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
user avatar
31 votes
1 answer
29k views

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in ...
user avatar
  • 1,789
8 votes
2 answers
13k views

Updating a table efficiently using JOIN

I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two ...
user avatar
  • 419
35 votes
8 answers
50k views

What is the difference between an INNER JOIN and an OUTER JOIN ?

I am new to SQL and wanted to know what is the difference between those two JOIN types? SELECT * FROM user u INNER JOIN telephone t ON t.user_id = u.id SELECT * FROM user u LEFT OUTER JOIN ...
user avatar
  • 509
10 votes
5 answers
30k views

Build JSON object from one-to-many relationship data in a single query?

I have a PostgreSQL 9.5.3 DB with tables like this: container id: uuid (pk) ... other data thing id: uuid (pk) ... other data container_thing container_id: uuid (fk) ...
user avatar
  • 235
43 votes
2 answers
2k views

Why does changing the declared join column order introduce a sort?

I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique. The test setup Setup script, including some ...
user avatar
13 votes
2 answers
61k views

SQL join query to show rows with non-existent rows in one table

I am trying to get some reporting done for employee time records. We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of ...
user avatar
6 votes
2 answers
67k views

Join two tables and return data and count in a single query

I have two tables in an SQL Server database, one with two columns and one with four: tbl_email_list email_list_id int (PK) email_list_name varchar tbl_email-details email_uniq_id int (PK) ...
user avatar