Questions tagged [outer-join]

An outer join defines a relationship between two tables where all records from one or both tables are returned regardless of the existence of a matching key-field in the other table. A full outer join combines the results of both tables. A left or right join returns all the records from the first or second specified table, respectively. NULLS are filled in for matches on either side. A self-join compares a table to a copy of itself.

A full combines the results of both tables. A left or right returns all the records from the first or second specified table, respectively. NULLS are filled in for matches on either side. A compares a table to a copy of itself.

References

1671 questions
29
votes
3 answers

Are left outer joins associative?

It's easy to understand why left outer joins are not commutative, but I'm having some trouble understanding whether they are associative. Several online sources suggest that they are not, but I haven't managed to convince myself that this is the…
Tianxiang Xiong
  • 3,887
  • 9
  • 44
  • 63
28
votes
5 answers

Problems getting LEFT OUTER JOIN to work

I thought I understood how left outer joins work, but I have a situation that is not working, and I'm not 100% sure if the way I have my query structured is incorrect, or if it's a data issue. For background, I have the following MySQL table…
TheIcemanCometh
  • 1,055
  • 2
  • 17
  • 31
27
votes
3 answers

MySQL OUTER JOIN syntax error

Maybe a facepalm for you guys, but as a SQL query newbie, I'm having a syntax issue. Anyone know what's wrong? SELECT * FROM company C OUTER JOIN company_address A ON C.company_id = A.company_id WHERE A.company_id IS NULL Giving the error: #1064 -…
Sem
  • 4,477
  • 4
  • 33
  • 52
23
votes
3 answers

(Lazy) LEFT OUTER JOIN using the Hibernate Criteria API

I want to perform a LEFT OUTER JOIN between two tables using the Criteria API. All I could find in the Hibernate documentation is this method: Criteria criteria = this.crudService .initializeCriteria(Applicant.class) …
Markos Fragkakis
  • 7,499
  • 18
  • 65
  • 103
22
votes
3 answers

Remove Duplicates from LEFT OUTER JOIN

My question is quite similar to Restricting a LEFT JOIN, with a variation. Assuming I have a table SHOP and another table LOCATION. Location is a sort of child table of table SHOP, that has two columns of interest, one is a Division Key (calling it…
KG -
  • 7,130
  • 12
  • 56
  • 72
20
votes
7 answers

Linq to Entity with multiple left outer joins

I am trying to understand left outer joins in LINQ to Entity. For example I have the following 3 tables: Company, CompanyProduct, Product The CompanyProduct is linked to its two parent tables, Company and Product. I want to return all of the Company…
Bob
17
votes
2 answers

Outer merging two data frames in place in pandas

How can I outer merge two data frames in place in pandas? For example, assume we have these two data frames: import pandas as pd s1 = pd.DataFrame({ 'time':[1234567000,1234567005,1234567009], …
Franck Dernoncourt
  • 77,520
  • 72
  • 342
  • 501
17
votes
2 answers

Left outer join - how to return a boolean for existence in the second table?

In PostgreSQL 9 on CentOS 6 there are 60000 records in pref_users table: # \d pref_users Table "public.pref_users" Column | Type | Modifiers …
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
16
votes
4 answers

Hibernate/JPA: How to force implicit joins to use LEFT OUTER JOINS

There is a class Offer that has optional relationship to class Article. So that some offers article property holds a null value. If i use the following statement, everything works fine. I got all offers, even those that have no article. SELECT o…
Dangermouse
  • 290
  • 1
  • 3
  • 11
16
votes
1 answer

Does PostgreSQL have a limit on # of tables in a join?

Today when playing around with dynamic query generation I discovered that mysql has a hard maximum limit of how many tables can be used in a join: 61. This lead me to wonder about PostgreSQL, does PostgreSQL have a analogous limit? Note: I am asking…
Tom Neyland
  • 6,860
  • 2
  • 36
  • 52
16
votes
3 answers

Multiple LEFT JOINs - what is the "left" table?

I've been using this for years, so it is high time to understand it fully. Suppose a query like this: SELECT * FROM a LEFT JOIN b ON foo... LEFT JOIN c ON bar... The documentation tells us that T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] }…
vektor
  • 3,312
  • 8
  • 41
  • 71
15
votes
1 answer

Hibernate default joining for nullable many-to-one

I have a hibernate mapping like this in a ProductDfn class @ManyToOne( fetch = FetchType.LAZY, optional = true ) @JoinColumn( name = "productTypeFk", nullable = true ) public ProductType getProductType() { return productType; } Note that the…
Mike Q
  • 22,839
  • 20
  • 87
  • 129
14
votes
2 answers

Outer Join with ORM mapping in SQLAlchemy

I am using the ORM Mapping in SQLAlchemy 0.6.8. I have three tables (A, B and C), with no foreign keys between them. I am trying to join table A and B, and then left outer join that with C. I am expecting a named tuple, with fields A, B and C - with…
Oddthinking
  • 24,359
  • 19
  • 83
  • 121
14
votes
4 answers

Linq Sub-Select

How do I write a sub-select in LINQ. If I have a list of customers and a list of orders I want all the customers that have no orders. This is my pseudo code attempt: var res = from c in customers where c.CustomerID ! in (from o in orders…
Dan
  • 29,100
  • 43
  • 148
  • 207
14
votes
3 answers

C# Outer Apply in LINQ

How can I achieve Outer Apply in LINQ? I'm having a bit of a problem. Here's the SQL Query I'm using. SELECT u.masterID ,u.user ,h.created FROM dbo.Users u OUTER APPLY (SELECT TOP 1 * FROM UserHistory h where h.masterID = u.masterID…
Michael D. Irizarry
  • 6,186
  • 5
  • 30
  • 35