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
28
votes
5 answers

Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

I am not getting my head around this, and wondered if anyone may be able to help me with this. I have 2 Tables called RES_DATA and INV_DATA RES_DATA Contains my Customer as below CUSTOMER ID | NAME 1, Robert 2, John 3, Peter INV_DATA Contains…
Rob4236
  • 375
  • 1
  • 7
  • 12
27
votes
4 answers

Conditional Inner Join

I want to be able to inner join two tables based on the result of an expression. What I've been trying so far: INNER JOIN CASE WHEN RegT.Type = 1 THEN TimeRegistration ELSE DrivingRegistration AS RReg ON RReg.RegistreringsId = R.Id RegT is a join I…
KristianB
  • 1,403
  • 3
  • 25
  • 46
27
votes
4 answers

How to join two tables mysql?

I have two tables: services id client service and clients id name email How to list table service and bring together the customer name that the customers table? field customer services in the table has the id of the customer at the customer…
user345824
26
votes
2 answers

Is there a fundamental difference between INTERSECT and INNER JOIN?

I understand, that INNER JOIN is made for referenced keys and INTERSECT is not. But afaik in some cases, both of them can do the same thing. So, is there a difference (in performance or anything) between the following two expressions? And if there…
Jere
  • 1,196
  • 1
  • 9
  • 31
26
votes
3 answers

FIltering on the join?

Is there any argument, performance wise, to do filtering in the join, as opposed to the WHERE clause? For example, SELECT blah FROM TableA a INNER JOIN TableB b ON b.id = a.id AND b.deleted = 0 WHERE a.field = 5 As opposed to SELECT blah FROM…
Craig
  • 18,074
  • 38
  • 147
  • 248
26
votes
2 answers

mysql: why does left join not use an index?

I am facing a strange performance issue with a mysql query. SELECT `pricemaster_products`.*, `products`.* FROM `pricemaster_products` LEFT JOIN `products` ON `pricemaster_products`.`ean` = `products`.`products_ean` I explicitely want to use a left…
Majiy
  • 1,890
  • 2
  • 24
  • 32
25
votes
4 answers

Multiple INNER JOIN from the same table

I have a table of metals MetalID integer MetalName text MetalCode text Item table ItemID integer ItemName text ... Metal1 int Ref.-> metals.metalID Metal2 int Ref.-> metals.metalID Metal3 int Ref.-> metals.metalID I am…
NCFUSN
  • 1,624
  • 4
  • 28
  • 44
24
votes
2 answers

How to force an index on inner joined tables?

How do I force indexes on a query similar to this. I need to force an index on foo and bar individually. SELECT foo.*, bar.* FROM foo INNER JOIN bar ON foo.rel_id = bar.rel_id WHERE foo.status = 1 AND bar.status = 1
David
  • 16,246
  • 34
  • 103
  • 162
22
votes
2 answers

SQL Insert Into with Inner Join

I want to make my insert query which has an inner join to the Users table. The example of the tables is like this: Users: id | fullName | preferredName | email | mobile | password 1 | Pan Lim | Lim | limpan45@gmail.com |…
Lue Fang
  • 245
  • 1
  • 3
  • 5
22
votes
6 answers

Order by in Inner Join

I am putting inner join in my query.I have got the result but didn't know that how the data is coming in output.Can anyone tell me that how the Inner join matching the data.Below I am showing a image.There are two table(One or Two Table). According…
Mohit Kumar
  • 1,885
  • 5
  • 21
  • 24
22
votes
1 answer

what is the difference between join keyword and inner join keyword in oracle sql?

I can't find documentations on the key word join but I saw examples on the web using it. I was doing some experiment with it in Oracle hr schema, where I have table departments: deparment_name manager_id location_id A table…
Cici
  • 1,407
  • 3
  • 13
  • 31
22
votes
1 answer

Better way to select all columns from first table and only one column from second table on inner join

Graphical Explaination Table 1's columns: |a|b|c|d|e| Table 2's columns: |a|x|y|z| I want only a, b, c, d, e, x. I only want column 'a' from table 1, not column 'a' from table 2. Wordy Explaination I have two tables with one column sharing a…
Zamicol
  • 4,626
  • 1
  • 37
  • 42
20
votes
8 answers

How can I perform an inner join with two object arrays in JavaScript?

I have two object arrays: var a = [ {id: 4, name: 'Greg'}, {id: 1, name: 'David'}, {id: 2, name: 'John'}, {id: 3, name: 'Matt'}, ] var b = [ {id: 5, name: 'Mathew', position: '1'}, {id: 6, name: 'Gracia', position: '2'}, {id: 2, name:…
TechnoCorner
  • 4,879
  • 10
  • 43
  • 81
20
votes
10 answers

Searching a big mysql database with relevance

I'm building a rather large "search" engine for our company intranet, it has 1miljon plus entries it's running on a rather fast server and yet it takes up to 1 min for some search queries. This is how the table looks I tried create an index for it,…
Breezer
  • 10,410
  • 6
  • 29
  • 50
19
votes
3 answers

Returning multiple object types using hibernate using an inner join

I seem to be having some difficulty with a query in hibernate. I am performing an inner join on two tables. SELECT * FROM product p INNER JOIN warehouse w ON p.wid = w.id Product Table: id | name | wid | price | stock ..... Warehouse Table: id |…
Dan
  • 193
  • 1
  • 1
  • 4
1 2 3
…
99 100