The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.

SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

OR

SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

SQL Code:

SELECT foods.item_name,foods.item_unit, company.company_name,company.company_city FROM foods INNER JOIN company ON foods.company_id =company.company_id;

Copy

Output:

ITEM_NAME ITEM_ COMPANY_NAME COMPANY_CITY ------------------------- ----- ------------------------- -------------- Chex Mix Pcs Akas Foods Delhi Cheez-It Pcs Jack Hill Ltd London BN Biscuit Pcs Jack Hill Ltd London Mighty Munch Pcs Foodies. London Pot Rice Pcs Jack Hill Ltd London Jaffa Cakes Pcs Order All Boston

Example of SQL INNER JOIN using JOIN keyword

To get item name, item unit columns from foods table and company name, company city columns from company table, after joining these mentioned tables, with the following condition -

1. company id of foods and company id of company table must be same,

the following SQL statement can be used:

SQL Code:

SELECT foods.item_name,foods.item_unit, company.company_name,company.company_city FROM foods JOIN company ON foods.company_id =company.company_id;

Copy

Output:

ITEM_NAME ITEM_ COMPANY_NAME COMPANY_CITY ------------------------- ----- ------------------------- ------------- Chex Mix Pcs Akas Foods Delhi Cheez-It Pcs Jack Hill Ltd London BN Biscuit Pcs Jack Hill Ltd London Mighty Munch Pcs Foodies. London Pot Rice Pcs Jack Hill Ltd London Jaffa Cakes Pcs Order All Boston

Pictorial Presentation of SQL Inner Join of Company and Foods Tables:

SQL Code:

SELECT * FROM foods JOIN company ON foods.company_id =company.company_id;

Copy

Output:

ITEM_ID ITEM_NAME ITEM_ COMPAN COMPAN COMPANY_NAME COMPANY_CITY -------- ------------------------- ----- ------ ------ ------------------------- ------------- 1 Chex Mix Pcs 16 16 Akas Foods Delhi 6 Cheez-It Pcs 15 15 Jack Hill Ltd London 2 BN Biscuit Pcs 15 15 Jack Hill Ltd London 3 Mighty Munch Pcs 17 17 Foodies. London 4 Pot Rice Pcs 15 15 Jack Hill Ltd London 5 Jaffa Cakes Pcs 18 18 Order All Boston

Using JOIN Clause

SELECT * FROM Table1 JOIN Table2 ON Table1.column_name=Table2.column_name;

Copy

Using INNER JOIN Clause

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.column_name= Table2.column_name;

Here is INNER JOIN

SELECT * FROM tableX INNER JOIN tableY on tableX.X = tableY.Y;

SELECT tableX.*,tableY.* FROM tableX,tableY WHERE tableX.X = tableY.Y;

Here only the matching of both tableX and tableY have appeared in the result set.

Here is LEFT OUTER JOIN

SELECT tableX.*,tableY.* FROM tableX,tableY WHERE tableX.X = tableY.Y(+)

SELECT * FROM tableX LEFT OUTER JOIN tableY ON tableX.X= tableY.Y

Here all the rows from tableX that is left side of JOIN clause and all the rows with NULL values for unmatched columns from tableY that is the right side of JOIN clause have appeared.

Here is RIGHT OUTER JOIN

SELECT * FROM tableX RIGHT OUTER JOIN tableY ON tableX.X= tableY.Y

Here all the rows from tableY that is the right side of JOIN clause and all the rows with NULL values for unmatched columns from tableX that is left side of JOIN clause have appeared.

Here is FULL OUTER JOIN

SELECT * FROM tableX FULL OUTER JOIN tableY ON tableX.X= tableY.Y

Here all the matching rows from tableX and tableY and all the unmatched rows with NULL values for both the tables have appeared.

INNER JOIN ON vs WHERE clause

The WHERE clause, what is done is that all records that match the WHERE condition are included in the result set but an INNER JOIN is that, data not matching the JOIN condition is excluded from the result set.

Linking between two or more tables should be done using an INNER JOIN ON clause but filtering on individual data elements should be done with WHERE clause.

INNER JOIN is ANSI syntax whereas the WHERE syntax is more relational model oriented.

The INNER JOIN is generally considered more readable and it is a cartesian product of the tables, especially when you join lots of tables but the result of two tables JOIN'ed can be filtered on matching columns using the WHERE clause.

$result = mysql_query("SELECT * FROM `table1` INNER JOIN `table2` ON table1.primaryKey=table2.table1Id");

I'd like to extend this to multiple tables (all with the same foreign keys). I am trying the following code which is not returning anything. Can anyone point out what I'm doing wrong?

$result = mysql_query("SELECT * FROM `table1` INNER JOIN `table2` INNER JOIN table3 ON table1.primaryKey=table2.table1Id=table3.table1Id");

SQL INNER JOIN syntax

The following illustrates INNER JOIN syntax for joining two tables:

Code language: SQL (Structured Query Language) (sql)SELECT column1, column2 FROM table_1 INNER JOIN table_2 ON join_condition;

Let’s examine the syntax above in greater detail:

• The  table_1 and  table_2 are called joined-tables.
• For each row in the  table_1, the query find the corresponding row in the  table_2 that meet the join condition. If the corresponding row found, the query returns a row that contains data from both tables. Otherwise, it examines next row in the table_1, and this process continues until all the rows in the  table_1 are examined.

For joining more than two tables, the same logic was applied.

SQL INNER JOIN examples

SQL INNER JOIN – querying data from two tables example

In this example, we will use the products and categories tables in the sample database. The following picture illustrates the database diagram.

In the diagram above:

• One category can have many products.
• One product belongs to one and only one category.

Therefore, there is a many-to-one relationship between the rows in the categories table and rows in the products table. The link between the two tables is the categoryid column.

We need to query the following data from both tables:

• productID, productName from the products table.
• categoryName from the categories table.

The following query retrieves data from both tables:

Code language: SQL (Structured Query Language) (sql)SELECT productID, productName, categoryName FROM products INNER JOIN categories ON categories.categoryID = products.categoryID;

The join condition is specified in the INNER JOIN clause after the ON keyword as the expression:

Code language: SQL (Structured Query Language) (sql)categories.categoryID = products.categoryID

For each row in the products table, the query finds a corresponding row in the categories table that has the same categoryid. If there is a match between two rows in both tables, it returns a row that contains columns specified in the SELECT clause i.e., product id, product name, and category name; otherwise, it checks the next row in products table to find the matching row in the categories table. This process continues until the last row of the products table is examined.

SQL INNER JOIN – querying data from three tables

We can use the same techniques for joining three tables. The following query selects productID, productName, categoryName and supplier from the products, categories and suppliers tables:

Code language: SQL (Structured Query Language) (sql)SELECT productID, productName, categoryName, companyName AS supplier FROM products INNER JOIN categories ON categories.categoryID = products.categoryID INNER JOIN suppliers ON suppliers.supplierID = products.supplierID

Implicit SQL INNER JOIN

There is another form of the 

INNER JOIN called implicit inner join as shown below:

Code language: SQL (Structured Query Language) (sql)SELECT column1, column2 FROM table_1, table_2 WHERE join_condition;

In this form, you specify all joined tables in the FROM clause and put the join condition in the WHERE clause of the SELECT statement. We can rewrite the query example above using the implicit 

INNER JOIN as follows:

SELECT productID, productName, categoryName FROM products, categories WHERE products.categoryID = categories.categoryID;

To keep only the first matching record in a SQL join with a condition, you can use the LIMIT clause in the SELECT statement.

For example, let's say you have two tables, table1 and table2, and you want to keep only the first matching record from table2 for each record in table1 based on a condition:

SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t2.value = 'some_value' LIMIT 1;

This will return all the records from table1, and for each record, it will return only the first matching record from table2 where value is 'some_value'.

Note that the LIMIT clause should be placed at the end of the SELECT statement.

You can also use the ROW_NUMBER() function to achieve a similar result. This function assigns a unique row number to each row within a result set, starting at 1 for the first row. You can then use the row number to filter the result set and keep only the first matching record.

For example:

SELECT * FROM ( SELECT t1.*, t2.*, ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.id) as row_num FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t2.value = 'some_value' ) as results WHERE row_num = 1;

This will return all the records from table1, and for each record, it will return only the first matching record from table2 where value is 'some_value'.

Another method is;

To keep only the first matching record in an inner join with a "between" condition, you can use the ROW_NUMBER() function as mentioned above. This function assigns a unique row number to each row within a result set, starting at 1 for the first row. You can then use the row number to filter the result set and keep only the first matching record.

For example:

SELECT * FROM ( SELECT t1.*, t2.*, ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.id) as row_num FROM table1 t1 INNER JOIN table2 t2 ON table1.a = table2.a WHERE table1.b BETWEEN table2.b1 AND table2.b2 ) as results WHERE row_num = 1;

This will return all the records from table1 that have a matching record in table2 based on the condition table1.b BETWEEN table2.b1 AND table2.b2, and for each matching record in table1, it will return only the first matching record from table2.

Alternatively, you can use the DISTINCT keyword to remove duplicates from the result set. However, this will remove all duplicates, not just the ones that are caused by multiple matching records in table2.

For example:

SELECT DISTINCT * FROM table1 t1 INNER JOIN table2 t2 ON table1.a = table2.a WHERE table1.b BETWEEN table2.b1 AND table2.b2

This will return all the unique records from table1 that have a matching record in table2 based on the condition table1.b BETWEEN table2.b1 AND table2.b2, but it will not keep only the first matching record from table2.

All given answers are wrong. But I am getting "unexpected error" when I try to post the answer. I attached my response in a notepad text file.

hello Muhammad Faisal Habib

Here's a trick solution

The basic function for comparing strings is strcmp(). It takes two arguments - the names of the strings being compared. The function returns a value of 0 if the strings are the same, a value greater than zero if the first is greater than the second, or a value less than zero if the first is less than the second.

To avoid repetition, add a clouse comparison of values

WHERE strcmp(table2.b1,table2.b2)>0

Mathematics is a pure science , how to prove theorems is important and often a new elegant proof of a theorem is very welcome.Dear Muhammad Faisal Habib hope the trick idea was helpful.

My question to anyone interested is whether in programming, e.g. SQL, it is performance that matters or clean, elegant as mathematicians say proving theorems.

For me programming is more of a practical thing and elegance matters insofar as programs are efficient and easier to correct, but if you don't have an idea as in this case, you can use tricks.

Mathematics is a pure science , how to prove theorems is important and often a new elegant proof is very welcome.