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

Rails ActiveRecord :joins with LEFT JOIN instead of INNER JOIN

I have this code User.find(:all, :limit => 10, :joins => :user_points, :select => "users.*, count(user_points.id)", :group => "user_points.user_id") which generates following sql SELECT users.*, count(user_points.id)…
Jakub Arnold
  • 85,596
  • 89
  • 230
  • 327
69
votes
5 answers

INNER JOIN vs INNER JOIN (SELECT . FROM)

Is there any difference in terms of performance between these two versions of the same query? --Version 1 SELECT p.Name, s.OrderQty FROM Product p INNER JOIN SalesOrderDetail s on p.ProductID = s.ProductID --Version 2 SELECT p.Name, s.OrderQty FROM…
Karun
  • 853
  • 1
  • 7
  • 15
68
votes
11 answers

Update Query with INNER JOIN between tables in 2 different databases on 1 server

Need some SQL syntax help :-) Both databases are on the same server db1 = DHE db2 = DHE_Import UPDATE DHE.dbo.tblAccounts INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink ON DHE.dbo.tblAccounts.AccountCode =…
Charlez
  • 871
  • 3
  • 11
  • 17
61
votes
3 answers

Insert using LEFT JOIN and INNER JOIN

Hey all i am trying to figure out how to go about inserting a new record using the following query: SELECT user.id, user.name, user.username, user.email, IF(user.opted_in = 0, 'NO', 'YES') AS optedIn FROM user LEFT JOIN user_permission AS…
StealthRT
  • 10,108
  • 40
  • 183
  • 342
60
votes
3 answers

Eliminating duplicate values based on only one column of the table

My query: SELECT sites.siteName, sites.siteIP, history.date FROM sites INNER JOIN history ON sites.siteName = history.siteName ORDER BY siteName,date First part of the output: How can I remove the duplicates in siteName column? I want to…
Ned
  • 1,055
  • 9
  • 34
  • 58
59
votes
1 answer

How do I convert multiple inner joins in SQL to LINQ?

I've got the basics of LINQ-to-SQL down, but I've been struggling trying to get JOINs to work properly. I'd like to know how to convert the following to LINQ-to-SQL (ideally using method chaining, as that is my preferred format). SELECT …
Ed Sinek
  • 4,829
  • 10
  • 53
  • 81
55
votes
2 answers

Multiple INNER JOIN SQL ACCESS

Syntax Error (missing Operator) in query expression 'tbl_employee.emp_id = tbl_netpay.emp_id INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID INNER JOIN tbl_tax ON tbl_employee.emp_id - tbl_tax.emp_ID'. SELECT…
emerjohn12
  • 585
  • 1
  • 6
  • 11
54
votes
5 answers

Opposite Of An Inner Join Query

Table 1 2 columns: ID, Name Table 2 2 columns: ID, Name What is a query to show names from Table 1 that are not in table 2? So filtering out all the names in table 1 that are in table 2 gives the result query. Filtering is on ID not name.
Nick LaMarca
  • 8,076
  • 31
  • 93
  • 152
49
votes
1 answer

Performing Inner Join for Multiple Columns in the Same Table

I have a scenario which I'm a bit stuck on. Let's say I have a survey about colors, and I have one table for the color data, and another for people's answers. tbColors color_code , color_name 1 , 'blue' 2 , 'green' 3 ,…
Frankie Simon
  • 721
  • 1
  • 7
  • 14
46
votes
6 answers

Inner join of DataTables in C#

Let T1 and T2 are DataTables with following fields T1(CustID, ColX, ColY) T2(CustID, ColZ) I need the joint table TJ (CustID, ColX, ColY, ColZ) How this can be done in C# code in a simple way? Thanks.
Dhanapal
  • 14,239
  • 35
  • 115
  • 142
46
votes
5 answers

WHERE Clause vs ON when using JOIN

Assuming that I have the following T-SQL code: SELECT * FROM Foo f INNER JOIN Bar b ON b.BarId = f.BarId; WHERE b.IsApproved = 1; The following one also returns the same set of rows: SELECT * FROM Foo f INNER JOIN Bar b ON (b.IsApproved = 1) AND…
tugberk
  • 57,477
  • 67
  • 243
  • 335
44
votes
7 answers

How to have SQL INNER JOIN accept null results

I have the following query: SELECT TOP 25 CLIENT_ID_MD5, COUNT(CLIENT_ID_MD5) TOTAL FROM dbo.amazonlogs GROUP BY CLIENT_ID_MD5 ORDER BY COUNT(*) DESC; Which returns: 283fe255cbc25c804eb0c05f84ee5d52 864458 879100cf8aa8b993a8c53f0137a3a176 …
Tom Redman
  • 5,592
  • 4
  • 34
  • 42
43
votes
10 answers

MySQL query to get "intersection" of numerous queries with limits

Assume I have a single mySQL table (users) with the following fields: userid gender region age ethnicity income I want to be able to return the number of total records based on the number a user enters. Furthermore, they will also be…
the way
  • 413
  • 4
  • 5
42
votes
7 answers

Using left join and inner join in the same query

Below is my query using a left join that works as expected. What I want to do is add another table filter this query ever further but having trouble doing so. I will call this new table table_3 and want to add where table_3.rwykey =…
Will
  • 1,084
  • 5
  • 20
  • 42
41
votes
2 answers

When should I use an INNER -LOOP- JOIN instead of an INNER JOIN

Today I learned about a thing in SQL Server called INNER LOOP JOIN. What does this mean? (Google is not helping .. or should I say ... the blog posts about it are a bit .. technical and are blowing my mind). Also, what are some common scenarios that…
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
1
2
3
…
99 100