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
10
votes
2 answers

Oracle SQL Query Filter in JOIN ON vs WHERE

For inner joins, is there any difference in performance to apply a filter in the JOIN ON clause or the WHERE clause? Which is going to be more efficient, or will the optimizer render them equal? JOIN ON SELECT u.name FROM users u JOIN departments…
invertigo
  • 6,336
  • 5
  • 39
  • 64
10
votes
5 answers

How to do WHERE clause BEFORE INNER JOIN

How I do query like this ? select Distinct Station , Slot , SubSlot, CompID , CompName from DeviceTrace as DT DT.DeviceID = '1339759958' inner join CompList as CL where and DT.CompID = CL.CompID I need to do DT.DeviceID = '1339759958' before…
Night Walker
  • 20,638
  • 52
  • 151
  • 228
9
votes
7 answers

What are the steps followed by sql engine to execute the query..??

My question is not how to use inner join in sql. I know about how it matches between table a and table b. I'd like to ask how is the internal working of inner working. What algorithm it involves? What happens internally when joining multiple…
henry
  • 969
  • 2
  • 11
  • 21
9
votes
4 answers

JOINs vs SELECTing from many tables

I've been looking far and wide, but I can't find an answer, probably because I can't figure out the right way to ask the question. So here it is: is there any reason to prefer any of these two queries? SELECT * FROM table1, table2 WHERE table1.id =…
Sophivorus
  • 3,008
  • 3
  • 33
  • 43
9
votes
3 answers

inner join and where in() clause performance?

I can get same result for these queries, but which one is the fastest, and most efficient? where in() or inner join? SELECT `stats`.`userid`,`stats`.`sumpoint` FROM `stats` INNER JOIN users ON `stats`.`userid` = `users`.`userid` WHERE…
Okan Kocyigit
  • 13,203
  • 18
  • 70
  • 129
9
votes
3 answers

SQL Server 2016 select where in json array

I have a table [JsonTable], and the column [JsonData] save the json string, JsonData like: { "Names": ["John", "Joe", "Sam"] } How can I inner join this table like: SELECT* FROM [TestTable] AS T INNER JOIN [JsonTable] AS J ON T.[Name] IN…
Max
  • 4,439
  • 2
  • 18
  • 32
9
votes
4 answers

How I can group the results by day in this query with SQL Server?

sql fiddle demo here I have this table structure for Diary table: CREATE TABLE Diary ( [IdDiary] bigint, [UserId] int, [IdDay] numeric(18,0), [IsAnExtraHour] bit ); INSERT INTO Diary ([IdDiary], [UserId], [IdDay],…
Esraa_92
  • 1,558
  • 2
  • 21
  • 48
9
votes
3 answers

SQL INNER JOIN vs LEFT JOIN with a WHERE

I am trying to grasp SQL joins more intuitively. For example, learning how a RIGHT JOIN can just be re-written as a LEFT JOIN (by flipping the order of the tables) helped me understand much better the way that the two joins work. However, now I'm…
elmer007
  • 1,412
  • 14
  • 27
9
votes
2 answers

SQL INNER JOINing 2 Subqueries

I am trying to inner join these 2 subqueries (I think that's what it's called anyways) together where the branchName of the first query is equal to the branchName of the second query. However, they don't seem to want to join together, and with my…
user4914034
  • 105
  • 1
  • 1
  • 4
9
votes
1 answer

sqlalchemy: select specific columns from multiple join using aliases

This has be stumped for more than a day now and examples I could find have not worked. I am new to SQLALCHEMY and I find the documentation not very enlightening. The query (so far): prey = alias(ensembl_genes, name='prey') bait =…
John van Dam
  • 393
  • 2
  • 9
9
votes
4 answers

How to join two tables based on substring values of fields?

I am having problem with sql. I want to join two tables, employee and class instructor. Condition is that employee is having unid column like 'u0871457' where as class instructor is having EmplId as '00871457'. I just want to replace the first…
userx
  • 1,083
  • 5
  • 18
  • 36
9
votes
2 answers

Join on different columns

I want to join 2 dataframes and I'm using the intructions in the Julia guide: http://dataframesjl.readthedocs.org/en/latest/joins_and_indexing.html?highlight=join join(a, b, on = :ID, kind = :inner) Now, how I can join on columns with different…
Guillaume
  • 1,277
  • 2
  • 13
  • 21
9
votes
3 answers

Django ForeignKey with null=True, inner join, and left outer join

Let's say I have two Django models Person and Company as follows: - class Company(models.Model): name = models.CharField() class Person(models.Model): last_name = models.CharField(blank=True) first_name = models.CharField() company…
chefsmart
  • 6,873
  • 9
  • 42
  • 47
9
votes
5 answers

INNER JOIN condition in WHERE clause or ON clause?

I mistyped a query today, but it still worked and gave the intended result. I meant to run this query: SELECT e.id FROM employees e JOIN users u ON u.email=e.email WHERE u.id='139840' but I accidentally ran this query SELECT e.id FROM employees e…
chiliNUT
  • 18,989
  • 14
  • 66
  • 106
9
votes
4 answers

What is the difference between join in FROM clause and WHERE clause?

We have a Oracle 10g and most of our applications are running Oracle Forms 6i. I found that all of the queries written in views/packages/procedures/functions are JOINING tables at WHERE clause level. Example SELECT * FROM TABLE_A A, TABLE_B B, …
Ahmad
  • 12,336
  • 6
  • 48
  • 88
1 2 3
…
99 100