Stack Overflow
Stack Overflow

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
7
votes
3 answers

Need help with an Opposite to Inner join Query using LINQ

I have two tables in an XML Dataset. T1, T2. Each of the tables has a ID column. T1 has a list of Customers T2 has a list of Orders I want to build a LINQ query that returns only the ID of the customers that do not have orders. In other words…
Rick
  • 648
  • 1
  • 11
  • 25
7
votes
2 answers

Cross join behaviour (SQLServer 2008)

I have been trying to track down a problem with a query I have. The query is actually generated by hibernate from HQL but the resulting SQL doesn't do what I expect. Modifying the SQL slightly produces the correct result but I'm not sure why the…
Mike Q
  • 22,839
  • 20
  • 87
  • 129
7
votes
1 answer

Efficient way to simulate full outer join in MySQL?

According to Google search: since MySQL does not support full outer join, it could be simulated via union and/or union all. But both of these either remove genuine duplicates or show spurious duplicates. What would be correct and efficient way? This…
understack
  • 11,212
  • 24
  • 77
  • 100
7
votes
1 answer

outer join in a query builder with doctrine

I have an entity named PointsComptage.php and another one named Compteurs.php. This is the relations between them: // Compteurs.php /** * @var \PointsComptage * * @ORM\ManyToOne(targetEntity="PointsComptage", inversedBy="compteurs") *…
french_dev
  • 2,117
  • 10
  • 44
  • 85
7
votes
2 answers

Oracle's OUTER JOIN (+) on string - Migration PostgreSQL

I'm migrating a client's software database from Oracle to PostgreSQL, and I have some trouble understanding a query, what it does, and consequently how to migrate it. The query is: SELECT * FROM TBL1, TBL2, TBL3, TBL4 WHERE TBL3.Project_ID =…
Tiller
  • 436
  • 1
  • 4
  • 22
7
votes
5 answers

SQL Server 2005 RIGHT OUTER JOIN not working

I'm looking up access logs for specific courses. I need to show all the courses even if they don't exist in the logs table. Hence the outer join.... but after trying (presumably) all of the variations of LEFT OUTER, RIGHT OUTER, INNER and…
CheeseConQueso
  • 5,831
  • 29
  • 93
  • 126
7
votes
7 answers

What are some good examples where SQL's OUTER JOIN is used?

I often get asked the questions in an interview that "what is an outer join in SQL"? While it can be answered, I wonder what might be some classic and good real life examples where a (LEFT) OUTER JOIN is used?
nonopolarity
  • 146,324
  • 131
  • 460
  • 740
7
votes
2 answers

How can A left outer join B return more rows than are in A?

What is it wrong about this simple SQL outer join? select count(*) from A -- 25766 select count(*) from B -- 1242 select count(*) from A left outer join B on A.b = B.b -- 310176 return 25766, 1242 and 310176 rows respectively. (This is for…
Drux
  • 11,992
  • 13
  • 66
  • 116
7
votes
1 answer

Linq to Entities and LEFT OUTER JOIN issue with MANY:1 relations

Can somebody tell me, why does Linq to Entities translate many to 1 relationships to left outer join instead of inner join? Because there's referential constraint on DB itself that ensures there's a record in the right table, so inner join should be…
Robert Koritnik
  • 103,639
  • 52
  • 277
  • 404
7
votes
2 answers

matrix operations and component-wise addition using data.table

What is the best way to do component-wise matrix addition if the number of matrices to be summed is not known in advance? More generally, is there a good way to perform matrix (or multi-dimensional array) operations in the context of data.table? I…
Scott
  • 765
  • 4
  • 9
7
votes
5 answers

"Not Join" in R

I am looking for a quick way to do 'not join' (i.e. keep rows that didn't merge, or inverse of inner join). The way I've been doing is to use data.table for X and Y, then set key. For example: require(data.table) X <- data.table(category =…
tanvach
  • 389
  • 1
  • 5
  • 12
7
votes
1 answer

sqlalchemy and double outerjoin

I need to do double outer join on following 2 tables A and B to get presented result using SQLAlchemy ORM or SQL expressions. Table B should be outer joined twice to get joined 2 result sets (distinguished by c_id) that are for the same A records.…
godfryd
  • 537
  • 1
  • 4
  • 12
6
votes
3 answers

Canonical outer join zip function

If you consider the (implicit) indexes of each element of a list as their keys, then zipWith is sort of like a relational inner join. It only processes the keys for which both inputs have values: zipWith (+) [1..5] [10..20] == zipWith (+) [1..11]…
rampion
  • 87,131
  • 49
  • 199
  • 315
6
votes
1 answer

(+) syntax for outer joins in mysql

Possible Duplicates: Oracle “(+)” Operator Oracle (Old?) Joins - A tool/script for conversion? I have been somewhat spoiled by using Oracle for years. Now I am using mysql and cannot find a non-ansi version/shorthand version of outer joins in…
benstpierre
  • 32,833
  • 51
  • 177
  • 288
6
votes
2 answers

'LEFT JOIN' vs 'LEFT OUTER JOIN'

I know there is really no difference, but is 'LEFT JOIN' an ANSI form or are there any RDBMS's that will fail 'LEFT JOIN' and require 'LEFT OUTER JOIN'. [I am asking here so I can save a few clicks, form fillings, etc to get the correct ANSI…
ManiP
  • 713
  • 2
  • 8
  • 19
1 2 3
…
99 100