All Questions
19,970
questions
5232
votes
28
answers
2.6m
views
What is the difference between "INNER JOIN" and "OUTER JOIN"?
Also, how do LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN fit in?
- 57.2k
2180
votes
2
answers
2.2m
views
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN
in MySQL?
- 33.4k
1435
votes
7
answers
985k
views
What is the difference between JOIN and INNER JOIN?
Both these joins will give me the same results:
SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK
vs
SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK
Is there ...
- 163k
1210
votes
29
answers
2.6m
views
SQL Update from One Table to Another Based on a ID Match
I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number so that I am only working with account numbers.
I created a view ...
Boerseun
1119
votes
12
answers
719k
views
INNER JOIN ON vs WHERE clause
For simplicity, assume all relevant fields are NOT NULL.
You can do:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1, table2
WHERE
table1.foreignkey = table2.primarykey
...
- 16.4k
1114
votes
20
answers
637k
views
Join vs. sub-query
I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why.
I lack the theoretical knowledge to judge for ...
- 157k
962
votes
22
answers
1.0m
views
SQL JOIN: what is the difference between WHERE clause and ON clause?
What is the difference and what should go in each?
If I understand the theory correctly, the query optimizer should be able to use both interchangeably.
(Note: this question is not a duplicate of ...
- 77k
880
votes
15
answers
1.1m
views
How can I do a FULL OUTER JOIN in MySQL?
I want to do a full outer join in MySQL. Is this possible? Is a full outer join supported by MySQL?
- 21.9k
495
votes
12
answers
231k
views
Explicit vs implicit SQL joins
Is there any efficiency difference in an explicit vs implicit inner join?
For example:
SELECT * FROM
table a INNER JOIN table b
ON a.id = b.id;
vs.
SELECT a.*, b.*
FROM table a, table b
WHERE a.id =...
- 51.9k
482
votes
18
answers
688k
views
What is the syntax for an inner join in LINQ to SQL?
I'm writing a LINQ to SQL statement, and I'm after the standard syntax for a normal inner join with an ON clause in C#.
How do you represent the following in LINQ to SQL:
select DealerContact.*
from ...
- 33.9k
478
votes
13
answers
410k
views
SQL join: selecting the last records in a one-to-many relationship
Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What ...
- 7,817
381
votes
12
answers
1.6m
views
SQL Inner-join with 3 tables?
I'm trying to join 3 tables in a view; here is the situation:
I have a table that contains information of students who are applying to live on this College Campus. I have another table that lists the ...
- 4,327
330
votes
14
answers
582k
views
What is the difference between JOIN and UNION?
What is the difference between JOIN and UNION? Can I have an example?
- 61.6k
326
votes
27
answers
256k
views
SQL select join: is it possible to prefix all columns as 'prefix.*'?
I'm wondering if this is possible in SQL. Say you have two tables A and B, and you do a select on table A and join on table B:
SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id);
If table ...
- 7,859
322
votes
5
answers
316k
views
What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
Since PostgreSQL came out with the ability to do LATERAL joins, I've been reading up on it since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall ...
- 16.6k
299
votes
12
answers
301k
views
SQL left join vs multiple tables on FROM line?
Most SQL dialects accept both the following queries:
SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x
SELECT a.foo, b.foo
FROM a
LEFT JOIN b ON a.x = b.x
Now obviously when you need an outer join, the ...
- 18.9k
275
votes
5
answers
961k
views
JOIN two SELECT statement results
Is it possible to join the results of 2 sql SELECT statements in one statement?
I have a database of tasks where each record is a separate task, with deadlines (and a PALT, which is just an INT of ...
- 3,109
275
votes
4
answers
238k
views
Does the join order matter in SQL?
Disregarding performance, will I get the same result from query A and B below? How about C and D?
----- Scenario 1:
-- A (left join)
select *
from a left join b
on <blahblah>
...
- 27.7k
268
votes
5
answers
418k
views
What is a SQL JOIN, and what are the different types? [duplicate]
What is a SQL JOIN, and what are the different types?
- 10.3k
246
votes
12
answers
323k
views
Difference between natural join and inner join
What is the difference between a natural join and an inner join?
- 5,381
237
votes
8
answers
311k
views
Are "from Table1 left join Table2" and "from Table2 right join Table1" interchangeable?
For example, there are two tables:
create table Table1 (id int, Name varchar (10))
create table Table2 (id int, Name varchar (10))
Table1 data as follows:
Id Name
-------------
...
- 11.3k
204
votes
4
answers
319k
views
Oracle "(+)" Operator
I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them.
The DBMS is Oracle.
I did not understand a statement which read like this:
select ...
from a,b
...
- 5,707
200
votes
6
answers
244k
views
Implement paging (skip / take) functionality with this query
I have been trying to understand a little bit about how to implement custom paging in SQL, for instance reading articles like this one.
I have the following query, which works perfectly. But I would ...
- 9,736
197
votes
11
answers
777k
views
Can I use CASE statement in a JOIN condition?
The following image is a part of Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between sys.partitions and sys.allocation_units depends on the value of sys....
- 27.7k
183
votes
2
answers
440k
views
SQL join on multiple columns in same tables
I have 2 subqueries, but I'm having trouble joining columns together from the same tables. I tried:
SELECT * FROM
(SELECT userid, listid
FROM user_views_table
WHERE date='2013-05-15' AND view_type=...
- 3,055
169
votes
21
answers
8k
views
Joins are for lazy people?
I recently had a discussion with another developer who claimed to me that JOINs (SQL) are useless. This is technically true but he added that using joins is less efficient than making several requests ...
- 18.1k
164
votes
13
answers
62k
views
Subqueries vs joins
I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like:
WHERE id IN (SELECT id FROM ...)
The refactored query runs about 100x ...
- 5,832
154
votes
10
answers
176k
views
MySQL JOIN the most recent row only?
I have a table customer that stores a customer_id, email and reference. There is an additional table customer_data that stores a historical record of the changes made to the customer, i.e. when there'...
- 26.3k
152
votes
5
answers
145k
views
What's the difference between comma separated joins and join on syntax in MySQL? [duplicate]
I have a table Person with a column id that references a column id in table Worker.
What is the difference between these two queries? They yield the same results.
SELECT *
FROM Person
JOIN Worker
...
- 9,658
152
votes
4
answers
180k
views
Join between tables in two different databases?
In MySQL, I have two different databases -- let's call them A and B.
Is it possible to perform a join between a table that is in database A, to a table that is in database B?
- 7,353
152
votes
8
answers
207k
views
Which of the join and subquery queries would be faster and why? When I should prefer one over the other?
I have a join query
Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id
and a subquery query
Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)
Which would be faster ...
- 12.3k
149
votes
6
answers
273k
views
What's the best way to join on the same table twice?
This is a little complicated, but I have 2 tables. Let's say the structure is something like this:
*Table1*
ID
PhoneNumber1
PhoneNumber2
*Table2*
PhoneNumber
SomeOtherField
The tables can be joined ...
- 81.5k
148
votes
4
answers
490k
views
MySQL Multiple Joins in one query?
I have the following query:
SELECT
dashboard_data.headline,
dashboard_data.message,
dashboard_messages.image_id
FROM dashboard_data
INNER JOIN dashboard_messages
ON dashboard_message_id = ...
- 6,814
148
votes
2
answers
293k
views
Can we use join for two different database tables?
Can we use the join operation for two tables from different databases? If yes, how do I do it?
Both databases are on the same server and DBMS is the same.
- 8,441
145
votes
4
answers
455k
views
How can I join multiple SQL tables using the IDs?
I have 4 different tables that I want to join. The tables are structured with columns as follows:
TableA - aID | nameA | dID
TableB - bID | nameB | cID | aID
TableC - cID | nameC | date
TableD - ...
- 2,668
141
votes
5
answers
312k
views
sqlalchemy: how to join several tables by one query?
I have the following SQLAlchemy mapped classes:
class User(Base):
__tablename__ = 'users'
email = Column(String, primary_key=True)
name = Column(String)
class Document(Base):
...
- 1,883
139
votes
7
answers
553k
views
1052: Column 'id' in field list is ambiguous
I have 2 tables. tbl_names and tbl_section which has both the id field in them. How do I go about selecting the id field, because I always get this error:
1052: Column 'id' in field list is ambiguous
...
- 22.8k
129
votes
7
answers
182k
views
How to exclude rows that don't join with another table?
I have two tables, one has primary key other has it as a foreign key.
I want to pull data from the primary table, only if the secondary table does not have an entry containing it's key. Sort of an ...
- 13.3k
127
votes
3
answers
91k
views
SQLite - How do you join tables from different databases?
I have an application that uses a SQLite database and everything works the way it should. I'm now in the process of adding new functionalities that require a second SQLite database, but I'm having a ...
- 4,884
119
votes
9
answers
78k
views
What are the uses for Cross Join?
A cross join performs a cartesian product on the tuples of the two sets.
SELECT *
FROM Table1
CROSS JOIN Table2
Which circumstances render such an SQL operation particularly useful?
- 6,076
115
votes
4
answers
140k
views
Filter Table Before Applying Left Join
I have 2 tables, I want to filter the 1 table before the 2 tables are joined together.
Customer Table:
╔══════════╦═══════╗
║ Customer ║ State ║
╠══════════╬═══════╣
║ A ║ S ║
...
- 1,985
114
votes
16
answers
51k
views
Why isn't SQL ANSI-92 standard better adopted over ANSI-89?
At every company I have worked at, I have found that people are still writing their SQL queries in the ANSI-89 standard:
select a.id, b.id, b.address_1
from person a, address b
where a.id = b.id
...
- 47.7k
113
votes
16
answers
37k
views
Why are joins bad when considering scalability?
Why are joins bad or 'slow'. I know i heard this more then once. I found this quote
The problem is joins are relatively
slow, especially over very large data
sets, and if they are slow your
...
user34537
108
votes
3
answers
229k
views
FULL OUTER JOIN vs. FULL JOIN
Just playing around with queries and examples to get a better understanding of joins. I'm noticing that in SQL Server 2008, the following two queries give the same results:
SELECT * FROM TableA
FULL ...
- 6,934
101
votes
4
answers
112k
views
Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL [duplicate]
What is the difference in results between:
RIGHT JOIN and RIGHT OUTER JOIN
LEFT JOIN and LEFT OUTER JOIN ?
Can you please explain it through some examples?
- 8,973
100
votes
7
answers
109k
views
How to join only one row in joined table with postgres?
I have the following schema:
CREATE TABLE author (
id integer
, name varchar(255)
);
CREATE TABLE book (
id integer
, author_id integer
, title varchar(255)
, rating ...
- 41.1k
97
votes
6
answers
42k
views
Which table exactly is the "left" table and "right" table in a JOIN statement (SQL)?
What makes a given table the left table?
Is it that the table is indicated in the "From" part of the query?
Or, is it the left table because it is on the left hand side of the = operator?
Are the ...
- 3,005
93
votes
6
answers
353k
views
Left Join without duplicate rows from left table
Please look at the following query:
tbl_Contents
Content_Id Content_Title Content_Text
10002 New case Study New case Study
10003 New case Study New case Study
10004 New case Study ...
- 933
93
votes
5
answers
400k
views
How to do join on multiple criteria, returning all combinations of both criteria?
I am willing to bet that this is a really simple answer as I am a noob to SQL.
Given:
table1 has column 1 (criteria 1) column 2 (criteria 2) column 3
(metric 1)
table2 has column 1 (criteria 1) ...
- 4,767
91
votes
7
answers
253k
views
SQL Left Join first match only
I have a query against a large number of big tables (rows and columns) with a number of joins, however one of tables has some duplicate rows of data causing issues for my query. Since this is a read ...
- 1,871