An SQL join clause combines records from two or more tables or views.
Questions tagged [join]
2000 questions
35
votes
8 answers
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 telephone t ON t.user_id = u.id
When should I use one or…
Julien
- 509
- 1
- 4
- 7
9
votes
2 answers
Filter Condition Difference - Where Clause vs Join Condition
Quick easy filter question.
What would be the difference in output, or what impact would it have moving a filter condition out of a WHERE clause into the Join condition.
For example:
Select a1.Name, a2.State
from student a1
left join location a2 on…
LargeMarge
- 115
- 1
- 1
- 4
5
votes
2 answers
How can the LEFT JOIN return more results than the unjoined query?
Consider the following queries:
mysql> SELECT count(*) FROM list l WHERE l.source='blink';
+----------+
| count(*) |
+----------+
| 3372 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id…
dotancohen
- 1,085
- 6
- 16
- 27
5
votes
1 answer
SQLite JOIN ... USING - rowid works, but then it doesn't
This works.
SELECT *
FROM Demographics
JOIN MeasuresofBirthAndDeath
ON Demographics.rowid = MeasuresofBirthAndDeath.rowid;
This does not.
SELECT *
FROM Demographics
JOIN MeasuresofBirthandDeath
USING (rowid);
Why?
Kevin
- 153
- 1
- 6
4
votes
1 answer
SQL Joins and Relationships
I am confused by joins. I want to know how to choose the join based on the relationship between tables, or do joins have no dependency on relationship?
if relationship is "one-to-one" then "Inner join" is the best.
if relationship is "one-to-many"…
sujith karivelil
- 567
- 1
- 6
- 14
3
votes
0 answers
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?
FbaStack
- 31
- 1
2
votes
1 answer
Nesting inner to sum inner query's result
Suppose I have a query select a,b from xyz
The result would be:
a b
m1 10
m2 20
m3 30
Can I somehow sum all b's something like:
select sum(b) from (select a,b from xyz)
And it will give me result 60. Possible? Stupid question?…
Sachin Verma
- 789
- 4
- 9
- 15
2
votes
1 answer
Joining on a field with different values
I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually took lunch or break). The problem is, the schedule…
Dantalion88
- 21
- 1
2
votes
2 answers
Silly beginners question about joins
I have no previous experience of SQL but I just now have an assignment where I'm supposed to join 2 tables using different kinds of joins (on physical paper, not in an editor).
Table 1 is called Cities and has 2 columns, Cities and Countries.
Table…
Isaiah
- 21
- 1
2
votes
3 answers
what does this query do?
Can you explain me the bold part?
select company_code,
founder,
(select count(distinct lead_manager_code)
from Lead_Manager
where company_code = c.company_code
)
from…
deepak
- 21
- 1
2
votes
3 answers
Join 1 column to multiple columns in another table
I have 2 tables portmap and devices
Portmap has the following columns.
ID (which is the PK),
networkDeviceID (FK to device table),
port,
timestamp,
description,
connectedDeviceID (FK to device table)
Devices has the following columns.
ID (which…
heywould
- 23
- 1
- 1
- 4
2
votes
1 answer
Joining Tables in the FROM vs. the WHERE clause
First time caller here.
I have a question regarding best practices when joining tables. For example, both of the following queries return the same results:
SELECT i.id, p.first_name, p.last_name
FROM individuals i, profiles p
WHERE i.id =…
Steven L.
- 165
- 1
- 6
2
votes
1 answer
SELECT values with a condition and JOIN
I am not sure that is a "SELECT/JOIN" problem but I really don't know how to formulate the question. Perhaps you could suggest a better title
I have a table (table-a) with a list of items, I Want to associate the information of table-B.col2 when it…
Emmanuel
- 123
- 3
2
votes
1 answer
How do I correctly group output from multiple tables?
I have more than 2 tables where entries are associated with dates. Each table can have multiple entries per date. I want to produce output that lists a date, then lists the entries of each table for that date. e.g.
date | …
Angelo
- 121
- 2
1
vote
1 answer
making a where clause optional in a sql statement
Here is my current query:
`SELECT `performers`.`hash`, `performers`.`alias`,
`performers`.`date_updated`, `performers`.`status`,
IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`,
`images`.`image_hash_file`
FROM…
somejkuser
- 807
- 3
- 7
- 15