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

TSQL to find items not in another list by group

I have 2 tables as follows: | Table 1 | Table 2 | Column 1 | Column 2 | Column 1 |----------|----------|--------- |c1 |v1 | v1 |c1 |v2 | v2 |c1 |v4 | v3 |c2 |v2 | v4 |c2 …
Breaker
  • 319
  • 2
  • 11
4
votes
5 answers

SQL join to get the cartesian product of 2 columns out of 3 columns

I have the following table: create table #table ( time int, key char(1), val int ) with the following data: insert into #table (time, key, val) values (0,"a",1) insert into #table (time, key, val) values (0,"b",2) insert into #table (time,…
Jerome Provensal
  • 931
  • 11
  • 22
4
votes
1 answer

How do I convert a "legacy" left outer join statement in Oracle?

I have two tables (A and G) in an Oracle database that can be joined together based off an account number. The one caveat to this is that one of the tables (G) has about 80 fewer records than the other. When I query the two tables together, I need…
Dillie-O
  • 29,277
  • 14
  • 101
  • 140
4
votes
2 answers

mySQL outer join

I have 2 tables for which I need to run a query on Table1 has 2 fields: l_id, and name Table2 also has 2 fields: l_id, and b_id I need to run a query to get the "name" and "l_id" for all the entries in table1 that do not have an entry in table2…
Señor Reginold Francis
  • 16,318
  • 16
  • 57
  • 73
4
votes
3 answers

Efficiently query for the latest version of a record using SQL

I need to query a table for the latest version of a record for all available dates (end of day time-series). The example below illustrates what I am trying to achieve. My question is whether the table's design (primary key, etc.) and the LEFT OUTER…
c31983
  • 449
  • 4
  • 16
4
votes
1 answer

Does memsql support Full Outer Join?

I wanted to have a full outer join in memsql. Something like SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id Is it possible ?
Vivek Aditya
  • 1,145
  • 17
  • 46
4
votes
2 answers

SQL Outer Join - improper execution

I am working with learning SQL, I have taken the basics course on pluralsight, and now I am using MySQL through Treehouse, with dummy databases they've set up, through the MySQL server. Once my training is complete I will be using SQLServer daily at…
Dawn Deschain
  • 156
  • 10
4
votes
3 answers

Concatenate more than two tables horizontally in SQL Server

The Following is the schema +---------+---------+ | Employee Table | +---------+---------+ | EmpId | Name | +---------+---------+ | 1 | John | | 2 | Lisa | | 3 | Mike | | | …
user3812060
  • 108
  • 1
  • 2
  • 8
4
votes
2 answers

pandas - DataFrame expansion with outer join

First of all I am very new at pandas and am trying to lean so thorough answers will be appreciated. I want to generate a pandas DataFrame representing a map witter tag subtoken -> poster where tag subtoken means anything in the set {hashtagA} U {i |…
fakedrake
  • 6,528
  • 8
  • 41
  • 64
4
votes
3 answers

How do I emulate outer + list?

I want to call a function on all combinations of arguments. For that end I tried outer: > outer(c(0,6,7),c(100,10,1,0.1,0.01),FUN=list) Error in outer(c(0, 6, 7), c(100, 10, 1, 0.1, 0.01), FUN = list) : dims [product 15] do not match the length…
sds
  • 58,617
  • 29
  • 161
  • 278
4
votes
1 answer

Should you use outer join fetching with NHibernate and Sql Server 2008?

On outer join fetching, the Nhibernate documentation says: If your database supports ANSI or Oracle style outer joins, outer join fetching might increase performance by limiting the number of round trips to and from the database (at the…
Mark Rogers
  • 96,497
  • 18
  • 85
  • 138
4
votes
1 answer

SqlAlchemy - Many to Many outer join with conditions on join

Here is my code: Table('contacts', self.metadata, Column('id', PGUuid, primary_key=True), Column('first_name', String(150), nullable=False), Column('middle_name', String(150), nullable=True), Column('last_name', String(150),…
Mihai H
  • 3,291
  • 4
  • 25
  • 34
4
votes
1 answer

Rails Join - How to get fields with same name

I have two tables with same name and am doing a join Visits::Appointment name:string id:integer ... Places::Seatables name:string appointment_id:integer id:integer .... I want to do a outer join and get all the…
user3443720
  • 47
  • 6
4
votes
1 answer

Optimize MySQL Full outer join for massive amount of data

We have the following mysql tables (simplified for going straight to the point) CREATE TABLE `MONTH_RAW_EVENTS` ( `idEvent` int(11) unsigned NOT NULL, `city` varchar(45) NOT NULL, `country` varchar(45) NOT NULL, `ts` datetime NOT NULL, …
jmserra
  • 1,296
  • 4
  • 18
  • 34
4
votes
2 answers

Postgres left outer join appears to not be using table indices

Let me know if this should be posted on DBA.stackexchange.com instead... I have the following query: SELECT DISTINCT "court_cases".* FROM "court_cases" LEFT OUTER JOIN service_of_processes ON service_of_processes.court_case_id =…
nzifnab
  • 15,876
  • 3
  • 50
  • 65
1 2 3
…
99 100