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

Efficient alternative to Outer on sparse arrays in Mathematica?

Suppose I have two very large lists {a1, a2, …} and {b1, b2, …} where all ai and bj are large sparse arrays. For the sake of memory efficiency I store each list as one comprehensive sparse array. Now I would like to compute some function f on all…
groovybaby
  • 191
  • 2
9
votes
2 answers

Left Outer Join not returning all records from primary table

When I do a left outer join, I expect to get all the records that the query would return prior to adding the joined table, but it is only returning records that match the joined table (i.e: no record for '092387' exists in table 'documentation', so…
Ted Scheckler
  • 1,389
  • 4
  • 16
  • 34
9
votes
3 answers

SQL Alias of joined tables

I have a query like this: select a1.name, b1.info from (select name, id, status from table1 a) as a1 right outer join (select id, info from table2 b) as b1 on (a1.id = b1.id) I only want to include everything where…
Lincecum
  • 795
  • 3
  • 8
  • 11
9
votes
4 answers

How to do Outer Join on >2 Tables (Oracle)

I'm not sure how to describe my table structure, so hope this makes sense... I have 3 tables in hierarchical relationship such that A has a one to many relationship to B which in turn has a one to many relationship with C. The trick is that the…
sdoca
  • 7,832
  • 23
  • 70
  • 127
9
votes
1 answer

Is breaking out of an outer loop still applicable in Swift 2?

I am reading the book, Professional Swift by Michael Dippery @ 2015. And in the book, on page 25, he writes: "Both break and continue statements break out of the innermost loops. However, you can label loops, which enables you to break out of an…
George Lee
  • 814
  • 18
  • 34
9
votes
4 answers

What is the difference between join in FROM clause and WHERE clause?

We have a Oracle 10g and most of our applications are running Oracle Forms 6i. I found that all of the queries written in views/packages/procedures/functions are JOINING tables at WHERE clause level. Example SELECT * FROM TABLE_A A, TABLE_B B, …
Ahmad
  • 12,336
  • 6
  • 48
  • 88
9
votes
4 answers

How to use oracle outer join with a filter where clause

If i write a sql: select * from a,b where a.id=b.id(+) and b.val="test" and i want all records from a where corresponding record in b does not exist or it exists with val="test", is this the correct query?
Victor
  • 16,609
  • 71
  • 229
  • 409
9
votes
4 answers

merge.data.table with all=True introduces NA row. Is this correct?

Doing a merge between a populated data.table and another one that is empty introduces one NA row in the resulting data.table: a = data.table(c=c(1,2),key='c') b = data.table(c=3,key='c') b=b[c!=3] b # Empty data.table (0 rows) of 1 col:…
vsalmendra
  • 577
  • 1
  • 6
  • 13
8
votes
3 answers

How to generalize outer to n dimensions?

The standard R expression outer(X, Y, f) evaluates to a matrix whose (i, j)-th entry has the value f(X[i], Y[j]). I would like to implement the function multi.outer, an n-dimensional generalization of outer: multi.outer(f, X_1, ..., X_n), where f is…
kjo
  • 33,683
  • 52
  • 148
  • 265
8
votes
6 answers

Oracle (Old?) Joins - A tool/script for conversion?

I have been porting oracle selects, and I have been running across a lot of queries like so: SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id; ...and: SELECT last_name,…
mainstringargs
  • 13,563
  • 35
  • 109
  • 174
8
votes
7 answers

Compare inner join and outer join SQL statements

What is the difference between an inner join and outer join? What's the precise meaning of these two kinds of joins?
freenight
  • 1,069
  • 3
  • 13
  • 19
8
votes
3 answers

Do I need to add an index on a mySQL table if the ID column is the primary key?

I have a table in mySQL where the 'id' column is the PRIMARY KEY: CREATE TABLE `USERS` ( `ID` mediumint(9) NOT NULL auto_increment, ..... PRIMARY KEY (`ID`), KEY `id_index` (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=267 DEFAULT…
Tenakha
  • 183
  • 1
  • 2
  • 7
8
votes
8 answers

What is the purpose (or use case) for an outer join in SQL?

Is an outer join only used for analysis by the developer? I'm having trouble finding a use case for why you would want to include data in two or more tables that is unrelated or does not "match" your select criteria.
user4903
7
votes
3 answers

Outer/tensor product in R

Given p vectors x1,x2,...,xp each of dimension d, what's the best way to compute their tensor/outer/Kruskal product (the p-array X with entries X[i1,i2,..ip] = x1[i1]x2[i2]...xp[ip])? Looping is trivial, but stupid. Using repeated calls to outer…
MMM
  • 93
  • 1
  • 4
7
votes
4 answers

Linq-to-sql not producing multiple outer-joins?

I've got a strange problem regarding linq-to-sql, and i've really tried search around for it. Im designing a sql database and where have just recently tried to retrieve an object from it. The problem is with multiple joins. All my tables use…
Goff
  • 343
  • 3
  • 7
1 2 3
…
99 100