Stack Overflow
Stack Overflow

Questions tagged [inner-join]

A database operation that combines the values of 2 tables based on a condition, or relationship, that exists between those tables.

An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.

When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every record in table B) then returning all records which satisfy the join predicate.

Actual SQL implementations normally use other approaches like a hash join or a sort-merge join where possible, since computing the Cartesian product is very inefficient.

enter image description here

Resources :

6652 questions
18
votes
2 answers

Ignoring case sensitvity in dplyr joins

Is there a way to ignore case sensitvity when doing joins using dplyr? left, inner, and full? I see it works with select but this often a huge pain for me. I know I can convert the columns toupper or tolower before hand, but this would be a…
runningbirds
  • 6,235
  • 13
  • 55
  • 94
18
votes
2 answers

The multi-part identifier could not be bound - SubQuery

Schema: create table TableA (A1 int) create table TableB (B1 int, B2 int) create table TableC (C1 int) Problematic query: SELECT * FROM TableA a INNER JOIN TableB b ON b.B1=a.A1 INNER JOIN (SELECT TOP 1 * FROM TableC c …
Matthew Azkimov
  • 467
  • 1
  • 4
  • 17
17
votes
3 answers

Inner join 2 tables with same column names

I'm working on displaying the achievements from my minecraft server on my website. But I can't get it to work. function achievements() { global $id; $sql="SELECT * FROM achievements INNER JOIN stats ON…
Lars Kaptein
  • 197
  • 1
  • 1
  • 9
17
votes
1 answer

inner join with group by expression in oracle sql

I am new to sql, any help is appreciated. I have two tables, employees and jobs. employees contain a variable job_id (multiple employees can have the same job_ID). jobs contain variables job_id and job_title (one job_ID correspond to one job_title,…
Cici
  • 1,407
  • 3
  • 13
  • 31
17
votes
8 answers

Inner join 2 tables but return all if 1 table empty

I have 2 tables say A and B, and I want to do a join on them. Table A will always have records in it. When table B has rows in it, I want the query to turn all the rows in which table A and table B matches. (i.e. behave like inner join) However, if…
viv_acious
  • 2,429
  • 9
  • 34
  • 55
17
votes
3 answers

MySQL INNER JOIN - '=' vs 'like'

We're having some odd issues with MySQL inner joins. Basically, we get an odd error when using an '=' operator but using 'like' instead makes it work. Unfortunately, this is via ActiveRecord and no easy way to just slap 'like' in there instead,…
Eric Anderson
  • 1,047
  • 1
  • 9
  • 13
16
votes
8 answers

MySQL: Inner join vs Where

Is there a difference in performance (in mysql) between Select * from Table1 T1 Inner Join Table2 T2 On T1.ID = T2.ID And Select * from Table1 T1, Table2 T2 Where T1.ID = T2.ID ?
Victor
  • 8,309
  • 14
  • 80
  • 129
16
votes
1 answer

Django Inner Join Queryset

I'm working with Django and I need to do a queryset using two inner joins. I have three models A, B, and C and I want to do a query like the following in psql: SELECT DISTINCT a FROM A INNER JOIN B ON B.a_id = A.id INNER JOIN C ON C.b_id =…
user1998511
  • 455
  • 1
  • 8
  • 21
16
votes
2 answers

Mysql range check instead of index usage on inner join

I'm having a serious problem with MySQL (innoDB) 5.0. A very simple SQL query is executed with a very unexpected query plan. The query: SELECT SQL_NO_CACHE mbCategory.* FROM MBCategory mbCategory INNER JOIN ResourcePermission as rp ON…
Wojtek Rudziński
  • 689
  • 1
  • 5
  • 8
16
votes
1 answer

How to determine what is more effective: DISTINCT or WHERE EXISTS?

For example, I have 3 tables: user, group and permission, and two many2many relationships between them: user_groups and group_permissions. I need to select all permissions of given user, without repeats. Every time I encounter a similar problem, I…
defuz
  • 26,721
  • 10
  • 38
  • 60
15
votes
4 answers

inner join Vs scalar Function

Which of the following query is better... This is just an example, there are numerous situations, where I want the user name to be displayed instead of UserID Select EmailDate, B.EmployeeName as [UserName], EmailSubject from Trn_Misc_Email as…
The King
  • 4,600
  • 3
  • 39
  • 58
15
votes
14 answers

Is seven inner joins in a query too much?

I have a query that has 7 inner joins (because a lot of the information is distributed in other tables), a few coworkers have been surprised. I was wondering if they should be surprised or is having 7 inner joins normal?
seFausto
  • 522
  • 1
  • 8
  • 24
15
votes
6 answers

MySQL difference between two rows of a SELECT Statement

I am trying to make the difference of two rows in an mysql database. I have this table containing ID, kilometers, date, car_id, car_driver etc... Since I don't always enter the information in the table in the correct order, I may end up with…
user1108276
  • 263
  • 2
  • 3
  • 9
15
votes
2 answers

Mysql query to join three tables

I am using this query: SELECT a.sales_id, d.bus_title, a.cat_id FROM tbl_sales a INNER JOIN tb_category b ON a.cat_id = b.cat_id INNER JOIN tbl_business d ON d.bus_id = a.bus_id which produces this result: sales_id | bus_title …
Jimmy M
  • 1,627
  • 3
  • 14
  • 18
14
votes
3 answers

How to inner-join in Excel (eg. using VLOOKUP)

Is there a way to inner join two different Excel spreadsheets using VLOOKUP? In SQL, I would do it this way: SELECT id, name FROM Sheet1 INNER JOIN Sheet2 ON Sheet1.id = Sheet2.id; Sheet1: +----+------+ | ID | Name | +----+------+ | 1 | A | | …
Wabbage
  • 437
  • 3
  • 6
  • 18
1 2 3
…
99 100