Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
130
questions
24
votes
2
answers
9k
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
...
49
votes
8
answers
97k
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
...
2
votes
1
answer
2k
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, ...
127
votes
5
answers
318k
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
...
24
votes
2
answers
44k
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 ...
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, ...
7
votes
1
answer
6k
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 ...
4
votes
2
answers
13k
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 ...
8
votes
2
answers
36k
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 ...
6
votes
2
answers
16k
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
);
...
19
votes
2
answers
9k
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')
...
16
votes
4
answers
59k
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 ...
3
votes
1
answer
3k
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 ...
3
votes
2
answers
940
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;
+---------+------...
0
votes
3
answers
7k
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 ...
49
votes
4
answers
90k
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;...
33
votes
2
answers
79k
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":...
12
votes
3
answers
5k
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'),
...
10
votes
2
answers
103k
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 ...
9
votes
1
answer
15k
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 ...
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 ...
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);
...
4
votes
3
answers
746
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
...
3
votes
1
answer
5k
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 ...
1
vote
1
answer
967
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)?
...
27
votes
3
answers
148k
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...) -...
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 *
...
15
votes
1
answer
38k
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 ...
15
votes
2
answers
112k
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 ...
13
votes
1
answer
4k
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 ...
6
votes
7
answers
17k
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 ...
5
votes
2
answers
29k
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'
...
5
votes
1
answer
492
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 |...
3
votes
3
answers
2k
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....
3
votes
2
answers
2k
views
How to prevent PostgreSQL from rewriting OUTER JOIN queries?
My query is:
SELECT Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2
FROM tableA LEFT JOIN
(tableB FULL JOIN tableC ON (Bcol1 = Ccol1))
ON (Acol1 = Bcol1)
EXPLAIN ANALYZE gives ...
2
votes
1
answer
773
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 ...
1
vote
1
answer
7k
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:
...
1
vote
1
answer
1k
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 |
| ...
1
vote
1
answer
570
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 ...
0
votes
1
answer
2k
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 ...
120
votes
3
answers
179k
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, ...
44
votes
2
answers
122k
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 ...
43
votes
2
answers
3k
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 ...
40
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 *
...
34
votes
7
answers
51k
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 ...
34
votes
1
answer
38k
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 ...
24
votes
2
answers
43k
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 ...
16
votes
4
answers
53k
views
Casting an array of texts to an array of UUIDs
How can I cast an array of texts into an array of UUIDs?
I need to do a join between two tables: users and projects.
The users table has an array field named project_ids containing the project IDs ...
13
votes
2
answers
64k
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 ...
12
votes
2
answers
22k
views
MySQL not using indexes when joining against another table
I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with ...