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
8
votes
1 answer

IF and CASE statements in SELECT query with a subquery in them

Queries below return NULL if menu_items.parent is not 0 which is wrong. What I'm trying to do here is, if value of menu_items.parent row is 0 then return it as original value but if the value is not 0 then return the name (varchar) of the…
BentCoder
  • 12,257
  • 22
  • 93
  • 165
8
votes
2 answers

Hibernate update query with innerjoin

I have the following MySQL update query with inner join: UPDATE Country AS c INNER JOIN State s ON c.CountryID = s.CountryID INNER JOIN City cy On s.StateID = cy.StateID SET c.Active='Y', s.Active='Y',cy.Active='Y' WHERE …
edaklij
  • 4,121
  • 11
  • 31
  • 43
7
votes
1 answer

Correct way to use INNER JOIN statement using a bridge table

Basically I have a table "Products" with "Product_ID" and table "Sub_Categories" with "Sub_Category_ID". Products that have a "Sub_Category" are linked using a bridge table, "SubC_Prod_Bridge", they are linked by ID "Products" and…
TaylorMac
  • 8,882
  • 21
  • 76
  • 104
7
votes
4 answers

Optimizing MySQL query with expensive INNER JOIN

Using trial and error i've discovered that when removing a join from the below query it runs around 30 times quicker. Can someone explain why this would be and if it's possible to optimise the query to include the additional join without the…
John Mellor
  • 2,351
  • 8
  • 45
  • 79
7
votes
1 answer

Oracle Update statement with an Inner Join

I am trying to write a simple update statement with an inner join, but the way I would do this in SQL server does not seem to be working in ORACLE. Here is the Update: UPDATE D SET D.USER_ID = C.USER_ID FROM D INNER JOIN C ON D.MGR_CD = C.MGR_CD…
esastincy
  • 1,607
  • 8
  • 28
  • 38
7
votes
3 answers

SQL INSERT INTO with SELECT and INNER JOIN

I hope you can help me with this little problem. I am not quite sure of how to explain the situation to you, so I will just give it a try. What I am trying to do is the following: I want to insert some specific values and parameters (which I type in…
sXing
  • 71
  • 1
  • 1
  • 2
7
votes
1 answer

Debugging performance differences (and issues) between a query with a double-subquery, a single-subquery and an all inner-join statements

I have a complex business logic that requires me to perform a 2-levels nested query. The queries are generated by Django's ORM. At the bottom of the question I'll provide the queries as-is as well as a full EXPLAIN suitable to be viewed with PEV2,…
alexandernst
  • 14,352
  • 22
  • 97
  • 197
7
votes
3 answers

How to improve order by performance with joins in mysql

I am working on a social network tracking application. Even joins works fine with proper indexing. But when I add the order by clause the total query takes 100 times longer time to execute. The following query I used to get the twitter_users without…
Tamizharasan
  • 293
  • 1
  • 5
  • 18
7
votes
4 answers

Inner Join in PowerShell (without SQL)

How do we make Inner-Join or something a Cross-Join in PowerShell or PowerCLI? Even though im new to PowerCLI/PowerShell , I do have a basic grasp on them, yet have practically spent 2 days trying to figure this, going through numerous…
AdilZ
  • 1,107
  • 6
  • 27
  • 44
7
votes
1 answer

Postgres Inner join insert into and select from syntax

I am trying to make a forgot password interface with node and typescript. I want to insert a forgot password token into a forgot password database and I am using the below query to get this done. Basically I am using guid's as the tokens and trying…
user4796879
7
votes
4 answers

Yii2 innerJoin()

I want to implement a sql query in the following way: INNER JOIN `Product_has_ProductFeature` t ON `Product`.`id` = t.`productId` AND t.`productFeatureValueId` = 1 INNER JOIN `Product_has_ProductFeature` t1 ON `Product`.`id` = t1.`productId` AND…
sdafasdf
  • 99
  • 1
  • 1
  • 4
7
votes
4 answers

Multiples INNER JOIN is too slow SQL SERVER

I'm having a performance problem. I created a table that receives data from a file, I do a BULK INSERT. Then I do a SELECT with multiple INNER JOINs (11 inner joins) to insert into another table with the right data. When I run this SELECT, it takes…
Alexandre_Almeida
  • 131
  • 1
  • 3
  • 10
7
votes
3 answers

Why left join turns into inner join if inner join is included in the query?

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Atbl') DROP TABLE Atbl CREATE TABLE ATbl ( Id int unique, AName varchar(20), ) GO IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Btbl') …
schizofreindly
  • 177
  • 2
  • 13
7
votes
5 answers

which query is better and efficient - mysql

I came across writing the query in differnt ways like shown below Type-I SELECT JS.JobseekerID , JS.FirstName , JS.LastName , JS.Currency , JS.AccountRegDate , JS.LastUpdated , JS.NoticePeriod …
gmhk
  • 15,598
  • 27
  • 89
  • 112
7
votes
1 answer

dplyr inner_join with NAs on character columns

I have two equal data frames a <- c(1,2,3) b <- c(3,2,1) c <- c('a','b',NA) df1 <- data.frame(a=a, b=b, c=c, stringsAsFactors=FALSE) df2 <- data.frame(a=a, b=b, c=c, stringsAsFactors=FALSE) I would like to use dplyr::inner_join to "return all…
tospig
  • 7,762
  • 14
  • 40
  • 79
1 2 3
…
99 100