Stack Overflow
Stack Overflow

Questions tagged [full-outer-join]

A full outer join combines the effect of applying both left and right outer joins.

Conceptually, a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).

243 questions
728
votes
15 answers

How to 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
  • 17,912
  • 32
  • 79
  • 116
212
votes
10 answers

SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN?

What is the difference between CROSS JOIN and FULL OUTER JOIN in SQL Server? Are they the same, or not? Please explain. When would one use either of these?
Saajid Ismail
  • 7,121
  • 10
  • 43
  • 55
211
votes
16 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
  • 5,161
  • 2
  • 31
  • 44
61
votes
4 answers

FULL OUTER JOIN with SQLite

SQLite only has INNER and LEFT JOIN. Is there a way to do a FULL OUTER JOIN with SQLite?
Yada
  • 27,686
  • 20
  • 97
  • 138
37
votes
6 answers

Multiple FULL OUTER JOIN on multiple tables

I have multiple outer joins SELECT A.column2 , B.column2 , C.column2 FROM ( (SELECT month, column2 FROM table1) A FULL OUTER JOIN (SELECT month, column2 FROM table2) B on A.month= B.month FULL OUTER JOIN …
Sandip Bantawa
  • 2,514
  • 4
  • 27
  • 43
36
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
  • 33,989
  • 61
  • 233
  • 360
25
votes
3 answers

MySQL: FULL OUTER JOIN - How do I merge one column?

I have a question regarding a FULL OUTER JOIN in MySQL. I have two (or more tables): table1 table2 id value id value2 1 a 1 b 2 c 3 d 3 e 4 f I have used this query to get my join: SELECT * FROM table1 LEFT…
Mig Cervantez
  • 253
  • 1
  • 3
  • 4
14
votes
3 answers

Find difference between two big tables in PostgreSQL

I have two similar tables in Postgres with just one 32-byte latin field (simple md5 hash). Both tables have ~30,000,000 rows. Tables have little difference (10-1000 rows are different) Is it possible with Postgres to find a difference between these…
odiszapc
  • 3,916
  • 2
  • 24
  • 42
10
votes
1 answer

SQLite3 Simulate RIGHT OUTER JOIN with LEFT JOINs and UNION

I have the following select statement where I need to sum each task from table tbTasks and group them by projectId from table tbProjects in order to get a record like this: ProjectID = 1, ProjectName = 'My Project', TotalTime = 300 //<--sum of each…
sadmicrowave
  • 35,768
  • 34
  • 99
  • 172
10
votes
5 answers

MySQL FULL JOIN not working but RIGHT and LEFT join works

This is driving me nuts. I have two tables that I am attempting to preform a join on, usersXstats and usersXstats_alltime. Both tables have the same columns: id, userId, statId, and value What I am trying to do is SELECT * FROM usersXstats FULL…
Alex
  • 115
  • 1
  • 2
  • 6
7
votes
2 answers

Full outer join on one criteria, inner join on another

i need to join Header and Detail rows into one resultset: (sample DDL and inserts to follow): Orders: OrderID OrderDate CurrencyID BuyAmount BuyRate ======= ======================= ========== ========= ======== 1 …
Ian Boyd
  • 220,884
  • 228
  • 805
  • 1,125
6
votes
2 answers

Why does full outer join in HIVE gives weird result when one of the join fields is missing?

I'm comparing the behavior between SQL engines. Oracle has the behavior I would expect from a SQL engine for full outer joins: Oracle CREATE TABLE sql_test_a ( ID VARCHAR2(4000 BYTE), FIRST_NAME VARCHAR2(200 BYTE), LAST_NAME…
makansij
  • 7,473
  • 28
  • 82
  • 156
6
votes
2 answers

MySQL select boolean based on whether record exists in another table

Hi I have a table of entities which a user can select, I also have another table which contains information about a user's favourite entities. See: Table Entity: id | ... Table SavedEntity: id | entity_id | user_id I want the results to be…
Josh
  • 3,280
  • 3
  • 34
  • 52
5
votes
3 answers

MySQL outer join substitute

I am working on a game inventory management system and would like to display the owner's restock wish list and a count of customer buy reservations for each game in a single table. I wrote a query that I thought was working, but then I noticed that…
ComicDavid
  • 55
  • 5
5
votes
4 answers

How to do a full outer join without having full outer join available

Last week I was surprised to find out that sybase 12 doesn't support full outer joins. But it occurred to me that a full outer join should be the same as a left outer join unioned with a right outer join of the same sql. Can anybody think of a…
stu
  • 7,445
  • 17
  • 69
  • 93
1
2 3
…
16 17