Questions tagged [join]

A JOIN is a general operation in relational algebra for a combining operation on two relations in a relational database system. JOIN is also a keyword of the SQL language for performing analogous SQL operations.

An SQL JOIN returns rows combined from two tables and possibly satisfying a condition.

ISO/ANSI standard SQL specifies numerous JOINs.

Unconditional/Cross joins:

  • CROSS JOIN
  • comma (implicit join)

Comma returns a CROSS JOIN but has lower precedence than the keyword joins.

Conditional joins:

  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

Each of those has an ON version, a USING version and a NATURAL version. OUTER is an optional keyword with no effect.

CROSS JOIN returns the rows that can be made by combining a row from the left table with a row from the right table. INNER JOIN ON/USING does a CROSS JOIN then keeps only rows satisfying a condition. LEFT/RIGHT/FULL OUTER JOIN ON/USING does an INNER JOIN then via UNION ALL adds the rows got by NULL-extending the rows from the LEFT/RIGHT/both input tables that did not form an INNER JOIN row.

Specific join tags:

You can specify your question by adding extra tags:

Questions:

43295 questions
6
votes
1 answer

Improve SQLite anti-join performance

Check out the update at the bottom of this question, the cause of the unexpected variance in query times noted below has been identified as the result of a sqliteman quirk I have the following two tables in a SQLite DB (The structure might seem…
Byron Bos
  • 63
  • 4
6
votes
3 answers

How to join all lines till next condition?

I can't find out how to join all lines till a next condition happens (a line with only 1 or more numbers) p.e. input: 1 text text text text (with numbers) text text text text (with numbers) 2 this text text text text text (with…
Reman
  • 7,931
  • 11
  • 55
  • 97
6
votes
3 answers

Can not determine what the WHERE clause should be

I'm stuck with creating a MySQL query. Below is my database structure. authors (author_id and author_name) books (book_id and book_title) books_authors is the link table (book_id and author_id) Result of all books and authors: I need to get all…
6
votes
1 answer

Postgres JOIN implementation

Could someone explain or point me to a resource that explains how a relational database (like Postgres or MySQL, I use Postgres more though) implements joins? For instance, I can roughly tell you that indexes might be made of a B tree where nodes…
tau
  • 6,499
  • 10
  • 37
  • 60
6
votes
1 answer

Python: Joining tuples with backslash

I'm attempting to join a tuple of two strings using the .join method as follows. >>> my_tuple = ("parent", "child") >>> "\\".join(my_tuple) I would expect this to return parent\child, however, it returns parent\\child. Why is this? Escaping the…
Evidex
  • 63
  • 1
  • 5
6
votes
4 answers

maximum and minimum number of tuples in natural join

I came across a question that states Consider the following relation schema pertaining to a students database: Student (rollno, name, address) Enroll (rollno, courseno, coursename) where the primary keys are shown underlined. The number of…
user1765876
6
votes
1 answer

merging in pandas vs merging in R

I'm afraid I do not quite understand the merging capabilities of pandas, although I much prefer python over R for now. In R, I have always been able to merge dataframes very easily as follows: > merge(test,e2s, all.x=T) Gene …
alternate direction
  • 622
  • 1
  • 8
  • 21
6
votes
2 answers

ElasticSearch Join Filter: Using subquery results as filter input possible?

I have a Use Case where I want to use ElasticSearch for realtime analytics. Within that, I want to be able to calculate some simple affinity scores. Those are currently defined using the number of transactions a filtered-by-criteria user base…
Tobi
  • 31,405
  • 8
  • 58
  • 90
6
votes
3 answers

How do I cross-join a table with a list?

If I have a table mytable and a list set vals = (1,2,3,4); and I want to cross-join the table with the list (getting a new table which has 4 time as many rows as the original table and an extra val column), do I have a better option than creating…
sds
  • 58,617
  • 29
  • 161
  • 278
6
votes
2 answers

Combine Multiple Query Results in MySQL (by column)

I have 4 different queries and each of them return individual unique set of Results. I need to combine the Query Results with using a single query. my sample queries are: 1. select cls.* from (calls as cls inner join calls_users as clsusr on…
Imrul
  • 3,456
  • 5
  • 32
  • 27
6
votes
0 answers

SQL Performance using JOIN instead of IN CLAUSE

I see a lot of places suggesting using INNER JOIN's instead of writing IN CLAUSES because of performance reasons. None of them explain why, though. Can someone provide a detailed explanation why that is?
Glide
  • 20,235
  • 26
  • 86
  • 135
6
votes
3 answers

Compound index required to speed up join-ed query?

A colleague asked me to explain how indexes (indices?) boost up performance; I tried to do so, but got confused myself. I used the model below for explanation (an error/diagnostics logging database). It consists of three tables: List of business…
naivists
  • 32,681
  • 5
  • 61
  • 85
6
votes
2 answers

#1054 - Unknown column in 'on clause'

I am getting this error: #1054 - Unknown column 'examinationresults.userid' in 'on clause' I've seen other posts on this but I can't seem to figure it out. I don't think I'm referring to anything out of order... SELECT examinationresults.id AS…
Edward
  • 518
  • 3
  • 13
6
votes
3 answers

Combine two tables in SQLite

I have two tables, ta and tb: ta: key col1 -------- k1 a k2 c tb: key col2 ------- k2 cc k3 ee They connected by "key". I want to know how can I get a table, tc, like: key col1 col2 ------------- k1 a k2 c cc k3 ee Is there a easy…
trudger
  • 917
  • 2
  • 12
  • 20
6
votes
1 answer

how to append every row of pandas dataframe to every row of another dataframe

for example, df1 is a 3*2 dataframe, and df2 is a 10*3 dataframe, what I want is to generate a new dataframe of 30*5, where each row in df1 is appended with the 3 columns of df2 for all 10 rows in df2. I know I can use iteration to append columns of…
user2963165
  • 61
  • 1
  • 2
1 2 3
99
100