Questions tagged [join]

A JOIN is a general operation in relational algebra for a combining operation on two relations in a relational database system. JOIN is also a keyword of the SQL language for performing analogous SQL operations.

An SQL JOIN returns rows combined from two tables and possibly satisfying a condition.

ISO/ANSI standard SQL specifies numerous JOINs.

Unconditional/Cross joins:

  • CROSS JOIN
  • comma (implicit join)

Comma returns a CROSS JOIN but has lower precedence than the keyword joins.

Conditional joins:

  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

Each of those has an ON version, a USING version and a NATURAL version. OUTER is an optional keyword with no effect.

CROSS JOIN returns the rows that can be made by combining a row from the left table with a row from the right table. INNER JOIN ON/USING does a CROSS JOIN then keeps only rows satisfying a condition. LEFT/RIGHT/FULL OUTER JOIN ON/USING does an INNER JOIN then via UNION ALL adds the rows got by NULL-extending the rows from the LEFT/RIGHT/both input tables that did not form an INNER JOIN row.

Specific join tags:

You can specify your question by adding extra tags:

Questions:

43295 questions
6
votes
2 answers

Hibernate Criteria: Perform JOIN in Subquery/DetachedCriteria

I'm running into an issue with adding JOIN's to a subquery using DetachedCriteria. The code looks roughly like this: Criteria criteria = createCacheableCriteria(ProductLine.class, "productLine"); criteria.add(Expression.eq("productLine.active",…
Gilean
  • 14,708
  • 10
  • 45
  • 52
6
votes
1 answer

How to avoid too many joins?

I would like your help to discuss how would I avoid too many joins using a generic approach. Is there a general rule for this? Currently, I have a very complex query that is joining 11 tables and the performance is very poor (even with indexes and…
Ricardo
  • 448
  • 4
  • 7
  • 19
6
votes
1 answer

Doctrine returns proxy object when joining table with nullable relations

I am trying to retrieve entities from one class joined with another class. Not all entities actually have joined entities. It's kinda like the following statement: SELECT a, b FROM A a LEFT JOIN B b ON a.id = b.aid GROUP BY a.id; or in…
van
  • 380
  • 3
  • 10
6
votes
3 answers

Ordering by a field not in the select statement in SQL

I need to create a query that pulls only the customer_no column (because the software restrictions are as such, and I can't code it externally). But I need to be able to sort the data by create_dt (in reverse) column. The code/SQL is restricting me…
YelizavetaYR
  • 1,611
  • 6
  • 21
  • 37
6
votes
3 answers

Linux join utility complains about input file not being sorted

I have two files: file1 has the format: field1;field2;field3;field4 (file1 is initially unsorted) file2 has the format: field1 (file2 is sorted) I run the 2 following commands: sort -t\; -k1 file1 -o file1 # to sort file 1 join -t\; -1 1 -2 1 -o…
Razvan
  • 9,925
  • 6
  • 38
  • 51
6
votes
4 answers

Replace returned null values in LEFT OUTER JOIN

SELECT WO_BreakerRail.ID, indRailType.RailType, indRailType.RailCode, WO_BreakerRail.CreatedPieces, WO_BreakerRail.OutsideSource, WO_BreakerRail.Charged, WO_BreakerRail.Rejected, WO_BreakerRail.RejectedToCrop, WO_BreakerRail.Date FROM …
Wesley
  • 659
  • 2
  • 13
  • 19
6
votes
1 answer

Prefetch related row or nothing with DBIx::Class, maybe with OUTER LEFT JOIN?

I want to retrieve rows from a table with DBIx::Class and prefetch respective rows from the same table where a column has a particular other value. I need to fetch all assignments from schedule A (to copy them) and retrieve all respective…
Daniel Böhmer
  • 14,463
  • 5
  • 36
  • 46
6
votes
1 answer

Pandas join on 2 columns

I'm having some trouble getting these two dfs to join the way I would like. The first df has a hierarchical index that I created using df1 = df3.groupby(["STATE_PROV_CODE", "COUNTY"]).size() to get the count for each county. STATE_PROV_CODE COUNTY …
jlmitch
  • 75
  • 1
  • 5
6
votes
6 answers

Is a JOIN more/less efficient than EXISTS IN when no data is needed from the second table?

I need to look up all households with orders. I don't care about the data of the order at all, just that it exists. (Using SQL Server) Is it more efficient to say something like this: SELECT HouseholdID, LastName, FirstName, Phone FROM Households…
twpc
  • 709
  • 3
  • 16
  • 26
6
votes
1 answer

Swift Array join EXC_BAD_ACCESS

Array –join(_:) function throws an EXC_BAD_ACCESS. var ar1 = [1,2,3] var ar2 = [5,6,7] var res = ar1.join(ar2) Has anyone faced this problem? Any solution or suggestion?
Kostiantyn Koval
  • 8,407
  • 1
  • 45
  • 58
6
votes
1 answer

Can mysql join occur on different data types

Suppose we have two tables, table A (parent) and table B (children) with a one to many relationship between them, established via parent_id (VARCHAR (100)) field in table B. The parent_id field's datatype is different from id (INT(11)) field in…
Haris ur Rehman
  • 2,593
  • 30
  • 41
6
votes
5 answers

Does clustered index on foreign key column increase join performance vs non-clustered?

In many places it's recommended that clustered indexes are better utilized when used to select range of rows using BETWEEN statement. When I select joining by foreign key field in such a way that this clustered index is used, I guess, that…
alpav
  • 2,972
  • 3
  • 37
  • 47
6
votes
7 answers

SQL Server 2008, join or no join?

Just a small question regarding joins. I have a table with around 30 fields and I was thinking about making a second table to store 10 of those fields. Then I would just join them in with the main data. The 10 fields that I was planning to store in…
Patrick
  • 5,442
  • 9
  • 53
  • 104
6
votes
1 answer

Parse.com equivalent to SQL joins

I know that Parse.com is a NoSql database and SQL concepts should not be applied to it, anyway, besides theory, i bet there is a way to do in Parse something like classic SQL joins. For example, assume we have 3 tables/classes in our DB: People,…
user3523425
6
votes
3 answers

How do I replace NOT EXISTS with JOIN?

I've got the following query: select distinct a.id, a.name from Employee a join Dependencies b on a.id = b.eid where not exists ( select * from Dependencies d where b.id = d.id and d.name = 'Apple' ) and exists ( …
YelizavetaYR
  • 1,611
  • 6
  • 21
  • 37
1 2 3
99
100