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
5190
votes
28 answers

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?
Chris de Vries
  • 56,777
  • 5
  • 32
  • 27
2143
votes
2 answers

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?
Lion King
  • 32,851
  • 25
  • 81
  • 143
2051
votes
13 answers

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

What is the difference between LEFT JOIN and LEFT OUTER JOIN?
KG Sosa
  • 21,565
  • 6
  • 26
  • 27
859
votes
15 answers

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?
Spencer
  • 21,348
  • 34
  • 85
  • 121
246
votes
18 answers

LINQ - Full Outer Join

I have a list of people's ID and their first name, and a list of people's ID and their surname. Some people don't have a first name and some don't have a surname; I'd like to do a full outer join on the two lists. So the following lists: ID …
ninjaPixel
  • 6,122
  • 3
  • 36
  • 47
201
votes
4 answers

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 where a.id=b.id(+) I am confused about the (+)…
Sekhar
  • 5,614
  • 9
  • 38
  • 44
158
votes
7 answers

LINQ to SQL - Left Outer Join with multiple join conditions

I have the following SQL, which I am trying to translate to LINQ: SELECT f.value FROM period as p LEFT OUTER JOIN facts AS f ON p.id = f.periodid AND f.otherid = 17 WHERE p.companyid = 100 I have seen the typical implementation of the left outer…
dan
  • 5,664
  • 8
  • 45
  • 59
119
votes
4 answers

Top 1 with a left join

Given the query below there might be multiple rows in dps_markers with the same marker key but we only want to join against the first. If I take this query and remove the top 1 and ORDER BY I get a value for mbg.marker_value but run as it is it…
dstarh
  • 4,976
  • 5
  • 36
  • 68
98
votes
4 answers

Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL

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?
Puru
  • 8,913
  • 26
  • 70
  • 91
83
votes
8 answers

How to return rows from left table not found in right table?

I have two tables with similar column names and I need to return records from the left table which are not found in the right table? I have a primary key(column) which will help me to compare both tables. Which join is preferred?
StarJedi
  • 1,410
  • 3
  • 21
  • 34
74
votes
7 answers

SQL SELECT from multiple tables

How can I get all products from customers1 and customers2 include their customer names? customer1 table cid name1 1 john 2 joe customer2 table cid name2 p1 sandy p2 linda product table pid cid pname 1 1 phone 2 2 pencil 3 p1 pen 4…
tjcombos
  • 753
  • 2
  • 6
  • 8
60
votes
2 answers

How can we differ LEFT OUTER JOIN vs Left Join

What is the difference between Left Join and Left Outer Join?
OM The Eternity
  • 15,694
  • 44
  • 120
  • 182
54
votes
5 answers

Opposite Of An Inner Join Query

Table 1 2 columns: ID, Name Table 2 2 columns: ID, Name What is a query to show names from Table 1 that are not in table 2? So filtering out all the names in table 1 that are in table 2 gives the result query. Filtering is on ID not name.
Nick LaMarca
  • 8,076
  • 31
  • 93
  • 152
37
votes
5 answers

How to do a full outer join in Linq?

I've inherited a database that wasn't designed exactly optimally, and I need to manipulate some data. Let me give a more common analogy of the kind of thing I have to do: Let's say we have a Student table, a StudentClass table keeping record of all…
Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
31
votes
3 answers

How does one do a full join using data.table?

In the data.table FAQ, the nomatch = NA parameter is said to be akin to an outer join. However, I haven't been able to get data.table to do a full outer join – only right outer joins. For example: a <- data.table("dog" = c(8:12), "cat" = c(15:19)) …
Paul Murray
  • 1,002
  • 1
  • 10
  • 24
1
2 3
…
99 100