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
14
votes
1 answer

sqlite LEFT OUTER JOIN multiple tables

In this example we have 3 related tables on a SQLite database: CREATE TABLE test1 ( c1 integer, primary key (c1) ); CREATE TABLE test2 ( c1 integer, c2 integer, primary key (c1, c2) ); CREATE TABLE test3 ( c2 integer, …
ferpega
  • 3,182
  • 7
  • 45
  • 65
13
votes
2 answers

how to search Sql Server 2008 R2 stored procedures for a string?

I'm migrating a legacy SQLS2k to 2008R2, and it seems all data access was done through stored procs, and any custom queries use the legacy *= =* outer join syntax. There are upwards of a hundred procs so I don't want to open each one individually to…
matao
  • 636
  • 3
  • 14
  • 22
13
votes
3 answers

LEFT OUTER JOIN with subquery syntax

I am learning SQL trough a GalaXQL tutorial. I can't figure out the following question (Exercise 12): Generate a list of stars with star ids below 100 with columns "starname", "startemp", "planetname", and "planettemp". The list should have all…
verkter
  • 758
  • 4
  • 15
  • 29
12
votes
2 answers

Rewrite left outer join involving multiple tables from Informix to Oracle

How do I write an Oracle query which is equivalent to the following Informix query? select tab1.a,tab2.b,tab3.c,tab4.d from table1 tab1, table2 tab2 OUTER (table3 tab3,table4 tab4,table5 tab5) where tab3.xya = tab4.xya AND tab4.ss =…
divya chekuri
  • 337
  • 5
  • 6
  • 10
12
votes
1 answer

Python pandas : Merge two tables without keys (Multiply 2 dataframes with broadcasting all elements; NxN dataframe)

I want to merge 2 dataframes with broadcast relationship: No common index, just want to find all pairs of the rows in the 2 dataframes. So want to make N row dataframe x M row dataframe = N*M row dataframe. Is there any rule to make this happen…
notilas
  • 2,323
  • 4
  • 23
  • 36
12
votes
2 answers

Full outer join in django

How can I create a query for a full outer join across a M2M relationchip using the django QuerySet API? It that is not supported, some hint about creating my own manager to do this would be welcome. Edited to add: @S.Lott: Thanks for the…
Ber
  • 40,356
  • 16
  • 72
  • 88
12
votes
1 answer

Multiple outer joins semantics

Some SQL code: SELECT * FROM table1 tab1 LEFT OUTER JOIN table2 tab2 ON (tab1.fg = tab2.fg) LEFT OUTER JOIN table4 tab4 ON (tab1.ss = tab4.ss) INNER JOIN table3 tab3 ON (tab4.xya = tab3.xya) LEFT OUTER JOIN table5 tab5 ON (tab4.kk =…
Howie
  • 2,760
  • 6
  • 32
  • 60
11
votes
4 answers

What's the best way to use LEFT OUTER JOIN to check for non-existence of related rows

Using MySQL 5.x I want to efficiently select all rows from table X where there is no related row in table Y satisfying some condition, e.g. Give me all records in X where a related Y with foo = bar does NOT exist SELECT count(id) FROM X LEFT OUTER…
podperson
  • 2,284
  • 2
  • 24
  • 24
11
votes
1 answer

what is the difference between left join and left outer join?

I have created 2 tables as CREATE TABLE table1(customerName VARCHAR(20),custid NUMBER ,order_id NUMBER ,price NUMBER ); CREATE TABLE table2(id NUMBER ,salary NUMBER ); Now, I tried to use the queries SELECT t1.customername,t1.custid,t2.salary FROM…
Shree Naath
  • 477
  • 2
  • 5
  • 18
11
votes
2 answers

how to speed up a vector cross product calculation

Hi I'm relatively new here and trying to do some calculations with numpy. I'm experiencing a long elapse time from one particular calculation and can't work out any faster way to achieve the same thing. Basically its part of a ray triangle…
user1942439
  • 157
  • 1
  • 8
11
votes
3 answers

data.table inner/outer join with NA in join column of type double bug?

Following this wikipedia article SQL join I wanted to have a clear view on how we could have joins with data.table. In the process we might have uncovered a bug when joining with NAs. Taking the wiki example: R) X =…
statquant
  • 13,672
  • 21
  • 91
  • 162
10
votes
1 answer

Can data table do a left join for 3 or more data tables?

I've searched for an answer to this simple question, but can't find a similar question. I have 3 data tables: set.seed(0) demo <- data.table(id = 1:10, demo.var = rnorm(10), key = 'id'); demo lab <- data.table(id = 1:7, tc = rnorm(7), key = 'id');…
David F
  • 1,506
  • 1
  • 12
  • 14
10
votes
1 answer

Outer join in Clojure

Similar to this question: Inner-join in clojure Is there a function for outer joins (left, right and full) performed on collections of maps in any of the Clojure libraries? I guess it could be done by modifying the code of clojure.set/join but this…
Goran Jovic
  • 9,418
  • 3
  • 43
  • 75
10
votes
2 answers

SQL left self-join with WHERE clause dependencies between the two copies of the table

The following two sentences: hello there bye! are represented in the table sentence_words by: WORD_ID SENTENCE_ID WORD WORD_NUMBER 10 1 hello 1 11 1 there 2 12 2 bye! 1 I want…
user984003
  • 28,050
  • 64
  • 189
  • 285
9
votes
2 answers

NHibernate - Left joins

I have the following two tables: Jobs AreaID, JobNo (composite key) Logs LogID, AreaID, JobNo I need to get all jobs that don't have any logs associated with them. In SQL I could do: SELECT Jobs.AreaID, Jobs.JobNo FROM Jobs LEFT JOIN…
Tom
  • 1,561
  • 4
  • 20
  • 29
1 2
3
…
99 100