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
41
votes
4 answers

Using DISTINCT inner join in SQL

I have three tables, A, B, C, where A is many to one B, and B is many to one C. I'd like a list of all C's in A. My tables are something like this: A[id, valueA, lookupB], B[id, valueB, lookupC], C[id, valueC]. I've written a query with two nested…
Mats Fredriksson
  • 19,783
  • 6
  • 37
  • 57
40
votes
5 answers

UPDATE statement with multiple joins in PostgreSQL

I'm trying to update a table called incode_warrants and set the warn_docket_no to the viol_docket_no from the incode_violations table. I have the following SQL query in Postgres 9.3, but when it fires I get the following error: Error : ERROR: …
nulltek
  • 3,247
  • 9
  • 44
  • 94
39
votes
5 answers

SQL Server - INNER JOIN WITH DISTINCT

I am having a hard time doing the following: select a.FirstName, a.LastName, v.District from AddTbl a order by Firstname inner join (select distinct LastName from ValTbl v where a.LastName = v.LastName) I want to do a join on ValTbl…
Nate Pet
  • 44,246
  • 124
  • 269
  • 414
39
votes
1 answer

Multiple inner joins with multiple tables

So I have four tables. Each table has a single id for the previous table id. So my in click table has an id and an id for the ad from which it came. In the ad table, it has an id for the ad and one for the campaign it's from. So here's an…
ATMathew
  • 12,566
  • 26
  • 69
  • 76
39
votes
4 answers

Inner join with 3 tables in mysql

I want to select data from more tables with Inner join. These are my tables. Student (studentId, firstName, lastname) Exam (examId, name, date) Grade (gradeId, fk_studentId, fk_examId, grade) I want to write a statement that shows which exam, grade…
Zincktest
  • 739
  • 2
  • 7
  • 9
36
votes
5 answers

How to make an Inner Join in django?

I want to show in an Html the name of the city, state, and country of a publication. But they are in different tables. Here is my models.py class country(models.Model): country_name = models.CharField(max_length=200, null=True) …
Sergio Mendez
  • 1,311
  • 8
  • 33
  • 56
35
votes
2 answers

Is CROSS JOIN a synonym for INNER JOIN without ON clause?

I am wondering whether CROSS JOIN can be safely replaced with INNER JOIN in any query when it is found. Is an INNER JOIN without ON or USING exactly the same as CROSS JOIN? If yes, has the CROSS JOIN type been invented only to express intent better…
Benoit
  • 76,634
  • 23
  • 210
  • 236
34
votes
7 answers

INNER JOIN vs multiple table names in "FROM"

Possible Duplicate: INNER JOIN versus WHERE clause — any difference? What is the difference between an INNER JOIN query and an implicit join query (i.e. listing multiple tables after the FROM keyword)? For example, given the following two…
Michael
  • 34,873
  • 17
  • 75
  • 109
34
votes
4 answers

Alias a table in Knex

I have a SQL query that refers to the same table twice, and I need to alias the table to two separate aliases. I can't quite figure out how to compose this with Knex. There's a 'Words' table and a 'Users' table. The Words table has two foreign keys,…
Bobby Circle Ciraldo
  • 1,243
  • 1
  • 10
  • 14
33
votes
4 answers

How to select all rows which have same value in some column

I am new to sql so please be kind. Assume i must display all the employee_ids which have the same phone number(Both columns are in the same table) How am i to proceed on this problem inner join or something.
Win Coder
  • 6,628
  • 11
  • 54
  • 81
33
votes
4 answers

using where and inner join in mysql

I have three tables. locations ID | NAME | TYPE | 1 | add1 | stat | 2 | add2 | coun | 3 | add3 | coun | 4 | add4 | coun | 5 | add5 | stat | schools ID | NAME 1 | sch1 2 | sch2 3 |sch3 school_locations ID…
sgi
  • 2,032
  • 6
  • 21
  • 28
32
votes
4 answers

SQL Inner join 2 tables with multiple column conditions and update

I am using this script, trying to join 2 tables with 3 conditions and update T1: Update T1 set T1.Inci = T2.Inci ON T1.Brands = T2.Brands AND T1.Category= T2.Category AND T1.Date = T2.Date but I encounter: Incorrect syntax near the keyword…
marilyn
  • 545
  • 3
  • 12
  • 19
31
votes
16 answers

SQL: Inner joining two massive tables

I have two massive tables with about 100 million records each and I'm afraid I needed to perform an Inner Join between the two. Now, both tables are very simple; here's the description: BioEntity table: BioEntityId (int) Name (nvarchar 4000,…
João Pereira
  • 3,545
  • 7
  • 44
  • 53
29
votes
7 answers

SQL INNER JOIN syntax

the two bits of SQL below get the same result SELECT c.name, o.product FROM customer c, order o WHERE c.id = o.cust_id AND o.value = 150 SELECT c.name, o.product FROM customer c INNER JOIN order o on c.id = o.cust_id WHERE o.value =…
Dean Madden
  • 341
  • 3
  • 6
  • 14
29
votes
2 answers

postgresql group by and inner join

I want a query in SQL which does INNER JOIN and GROUP BY at the same time. I tried the following which doesn't work: SELECT customer.first_name, SUM(payment.amount) FROM customer GROUP BY customer.customer_id INNER JOIN payment ON…
wonderbummer
  • 427
  • 1
  • 5
  • 11
1 2
3
…
99 100