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).

279 questions
2
votes
1 answer

Sorting by date across two separate columns in a Full Outer Join

I have two columns of data I am lining up using a Full Outer Join but it includes two separate date columns which make it challenging to sort by. Table 1 has sales rank data for a product. Table 2 has actual sales data for the same product. Each…
nycdan
  • 2,819
  • 2
  • 21
  • 33
2
votes
2 answers

Alternative to doing FULL OUTER JOIN for multiple tables?

I needed to run the following on MySQL, but it doesn't allow FULL OUTER JOINs: SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date, COALESCE(t0.hits1, 0) AS hits0, COALESCE(t1.hits2, 0) AS hits1, COALESCE(t2.hits3, 0) AS…
Jodes
  • 14,118
  • 26
  • 97
  • 156
2
votes
1 answer

How to join tables of events to report non-events?

Employees must complete continuing education modules to stay competent. I need to build a report showing supervisors which modules their employees have – and have not -- completed. I have a simple table of employees and their supervisors. Each row…
user18612109
  • 25
  • 5
2
votes
2 answers

Why is there NULL in the result of a full outer join between two tables?

I'm trying to get the unique values from two tables who both just have one column called domain. DDL: create table domains_1 (domain varchar); create table domains_2 (domain varchar); DML: insert into domains_1 values ('example_1.com'),…
JamesWang
  • 1,175
  • 3
  • 14
  • 32
2
votes
5 answers

FULL OUTER JOIN which is somewhat INNER

Here is the problem I'm facing to : I receive expected Xs from the client I receive realized Xs from the firm agencies I have to full outer join the expected and realized Xs on code equality AS LONG AS THEY BELONG TO THE SAME JOB FILE For example…
Ssithra
  • 710
  • 3
  • 8
2
votes
3 answers

Oracle outer join "entity"

How can you refer to the "combined entity" created from an outer join within your query? Specifically how can you replace the "??" in the query below: SELECT TableA.x, ??.y --How do you select from the combined entity? FROM TableA, …
Marcus Leon
  • 55,199
  • 118
  • 297
  • 429
2
votes
2 answers

Performance Comparison: Full outer join vs Union, Union All

I have tables named: mktActualsales (SaleID, EmployeeID, PeriodID,PositionID) mktActualSalesItems(SaleItemID, saleID, Item, Quantity) mktSalesTargets(TargetID, EmployeeID, PeriodID,PositionID) mktSalesTargetItems(TargetITemID, TargetID,ItemID,…
Muhammad Adeel Zahid
  • 17,474
  • 14
  • 90
  • 155
2
votes
2 answers

Three-way FULL OUTER JOIN / table coalesce

i want to perform a FULL OUTER JOIN, merging common rows, on three tables. SELECT * FROM Users id Username Fullname == ======== ===================== 7 iboyd Ian Boyd 8 nicholle Michelle Karnac 10 jamie Jimmy Chew 3 row(s)…
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
2
votes
1 answer

FULL OUTER JOIN with Rails 6 ActiveRecord

I am modeling an app with messages and notifications as follows (simplified): # db/schema.rb create_table :messages do |t| ... end create_table :notifications do |t| t.references :message, index: true, foreign_key: true,…
Sébastien Dubois
  • 869
  • 9
  • 19
2
votes
1 answer

Full Outer Self join with data for different date

I want to compare values with values from following day (and also want to see which color is new or didn't appear). I've did full outer self join and replaced null values in the section on the right of 'is_matched'. Is_matched is showing us if join…
snowboi
  • 101
  • 2
  • 12
2
votes
3 answers

Hive SQL Full Outer Join with Where Clause

I am creating a full outer join with a where clause. However, it can only generate inner join result. I suspect that it is due to the where clause, but I do need this where condition being added. So how can I create a query with both needs meet…
Counter10000
  • 525
  • 1
  • 8
  • 25
2
votes
2 answers

full outer join on two keys

I am trying to do merge two tables on phone numbers so that if I find phone in either of the tables then join the rest of the fields as shown below. Now there are scenarios where phone doesn't exist in both the tables. Then the table should join on…
Sumit Singh
  • 129
  • 10
2
votes
1 answer

How to compare two mysql tables side by side

I have two mysql tables which structurally are identical. What I need to do is to compare the content of both tables. There are many answers out there how to show the rows that appear in one table and not the other, but what I need is a little…
Typhoon101
  • 2,063
  • 8
  • 32
  • 49
2
votes
1 answer

Missing some rows when using FULL OUTER JOIN

I'm trying to join two tables with a FULL OUTER JOIN but I'm not getting what I expected because there is a a row missing. I'm trying to join both table in 3 different columns. Table A: CallId ASId DateTime CallStatus …
Charlyjauskas
  • 21
  • 1
2
votes
1 answer

SQLite: full outer join with four tables

EDIT: An extension to this question can be found here I want to join four different tables with SQLite which have just two columns in common. Please take a look at following example +--------+---+-----+-----+ | table1 | | | …
AndrejCoding
  • 127
  • 9
1 2
3
…
18 19