Содержание
- 1 INNER JOIN
- 2 Animated Version
- 2.1 SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN (‘Green’,’Blue’)
- 2.2 SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour
- 2.3 SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL
- 2.4 SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour
- 2.5 SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour
- 2.6 SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0
- 3 SQL LEFT OUTER JOIN
- 4 Introduction to the SQL INNER JOIN clause
- 5 Changes in the data
- 6 INNER JOIN / CROSS JOIN
- 7 Demo Database
- 8 SQL JOIN
- 9 LEFT OUTER JOIN
- 10 The troubles with the Venn diagram
- 11 When INTERSECT and UNION makes sense
- 12 Сложные и многотабличные запросы
- 13 SQL References
- 14 Demo Database
- 15 SQL INNER JOIN for all columns
- 16 Difference between JOIN and INNER JOIN
- 17 What is Inner Join in SQL?
- 18 SQL JOIN Examples
INNER JOIN
Let’s discuss these two queries:
1 2 3 4 5 6 7 |
SELECT* FROMcountry,city WHEREcity.country_id=country.id; SELECT* FROMcountry INNERJOINcityONcity.country_id=country.id; |
The result they return is presented on the picture below:
Both queries return exactly the same result. This is not by accident but the result of the fact that this is the same query written in two different ways. Both ways are correct, and you can use any of them.
In the first query, we listed all tables we use in the FROM part of the query (FROM country, city) and then went with the join condition in the WHERE part of the query (WHERE city.country_id = country.id). In case we forgot to write down this join condition, we would have the Cartesian product of both tables.
In the second query, we have only one table in the FROM part of the query (FROM country) and then we have the second table and the JOIN condition in the JOIN part of the query (INNER JOIN city ON city.country_id = country.id).
While both queries are well-written, I would suggest that you always use INNER JOIN instead of listing tables and joining them in the WHERE part of the query. There are a few reasons for that:
- Readability is much better because the table used and related JOIN condition are in the same line. You can easily see if you omitted the JOIN condition or not
- If you want to use other JOINs later (LEFT or RIGHT), you couldn’t do that (easily) unless you’ve used INNER JOIN before that
Now, let’s comment on what queries actually returned:
- All pairs of countries and cities that are related (via foreign key)
- We don’t have 2 countries in the list (Spain and Russia), because they don’t have any related city in the city table
Animated Version
SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN (‘Green’,’Blue’)
The inner join condition need not necessarily be an equality condition and it need not reference columns from both (or even either) of the tables. Evaluating on each row of the cross join returns.
SELECT A.Colour, B.Colour FROM A INNER JOIN B ON 1 =1
The join condition evaluates to true for all rows in the cross join result so this is just the same as a cross join. I won’t repeat the picture of the 16 rows again.
SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour
Outer Joins are logically evaluated in the same way as inner joins except that if a row from the left table (for a left join) does not join with any rows from the right hand table at all it is preserved in the result with values for the right hand columns.
SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL
This simply restricts the previous result to only return the rows where . In this particular case these will be the rows that were preserved as they had no match in the right hand table and the query returns the single red row not matched in table . This is known as an anti semi join.
It is important to select a column for the test that is either not nullable or for which the join condition ensures that any values will be excluded in order for this pattern to work correctly and avoid just bringing back rows which happen to have a value for that column in addition to the un matched rows.
SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour
Right outer joins act similarly to left outer joins except they preserve non matching rows from the right table and null extend the left hand columns.
SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour
Full outer joins combine the behaviour of left and right joins and preserve the non matching rows from both the left and the right tables.
SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0
No rows in the cross join match the predicate. All rows from both sides are preserved using normal outer join rules with NULL in the columns from the table on the other side.
With a minor amend to the preceding query one could simulate a of the two tables.
Note that the clause (if present) logically runs after the join. One common error is to perform a left outer join and then include a WHERE clause with a condition on the right table that ends up excluding the non matching rows. The above ends up performing the outer join…
… And then the «Where» clause runs. does not evaluate to true so the row preserved by the outer join ends up discarded (along with the blue one) effectively converting the join back to an inner one.
If the intention was to include only rows from B where Colour is Green and all rows from A regardless the correct syntax would be
SQL LEFT OUTER JOIN
Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax
The syntax for the LEFT OUTER JOIN in SQL is:
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
In some databases, the OUTER keyword is omitted and written simply as LEFT JOIN.
Visual Illustration
In this visual diagram, the SQL LEFT OUTER JOIN returns the shaded area:
The SQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.
Example
Now let’s look at an example that shows how to use the LEFT OUTER JOIN in a SELECT statement.
Using the same customers table as the previous example:
customer_id | last_name | first_name | favorite_website |
---|---|---|---|
4000 | Jackson | Joe | techonthenet.com |
5000 | Smith | Jane | digminecraft.com |
6000 | Ferguson | Samantha | bigactivities.com |
7000 | Reynolds | Allen | checkyourmath.com |
8000 | Anderson | Paige | NULL |
9000 | Johnson | Derek | techonthenet.com |
And the orders table with the following data:
order_id | customer_id | order_date |
---|---|---|
1 | 7000 | 2016/04/18 |
2 | 5000 | 2016/04/18 |
3 | 8000 | 2016/04/19 |
4 | 4000 | 2016/04/20 |
5 | NULL | 2016/05/01 |
Enter the following SQL statement:
Try It
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id;
There will be 6 records selected. These are the results that you should see:
customer_id | order_id | order_date |
---|---|---|
4000 | 4 | 2016/04/20 |
5000 | 2 | 2016/04/18 |
6000 | NULL | NULL |
7000 | 1 | 2016/04/18 |
8000 | 3 | 2016/04/19 |
9000 | NULL | NULL |
This LEFT OUTER JOIN example would return all rows from the customers table and only those rows from the orders table where the joined fields are equal.
Introduction to the SQL INNER JOIN clause
So far, you have learned how to use the SELECT statement to query data from a single table. However, the SELECT statement is not limited to query data from a single table. The SELECT statement can link multiple tables together.
The process of linking tables is called joining. SQL provides many kinds of joins such as inner join, left join, right join, full outer join, etc. This tutorial focuses on the inner join.
The inner join clause links two (or more) tables by a relationship between two columns. Whenever you use the inner join clause, you normally think about the intersection.
It is much easier to understand the inner join concept through a simple example.
Suppose, we have two tables: A & B.
Table A has four rows: (1,2,3,4) and table B has four rows: (3,4,5,6)
When table A joins with the table B using the inner join, we have the result set (3,4) that is the intersection of the table A and table B.
See the following picture.
For each row in table A, the inner join clause finds the matching rows in the table B. If a row is matched, it is included in the final result set.
Suppose the column name of the A & B tables is n, the following statement illustrates the inner join clause:
The INNER JOIN clause appears after the FROM clause. The condition to match between table A and table B is specified after the ON keyword. This condition is called join condition i.e.,
The INNER JOIN clause can join three or more tables as long as they have relationships, typically foreign key relationships.
For example, the following statement illustrates how to join 3 tables: A, B, and C:
Changes in the data
Before we compare INNER JOIN vs LEFT JOIN, let’s see what we currently know. So far, in this series, we’ve explained database basics – how to create database and tables, how to populate tables with data and check what’s stored in them using simple queries. We’ve even joined two tables in the previous article. Now we’re ready for the next step.
But before we move to it, let’s make just one minor change to our data. We’ll add 2 rows in the country table, using the following INSERT INTO commands:
1 2 |
INSERTINTOcountry (country_name,country_name_eng,country_code)VALUES(‘España’,’Spain’,’ESP’); INSERTINTOcountry (country_name,country_name_eng,country_code)VALUES(‘Rossiya’,’Russia’,’RUS’); |
Now we’ll check the contents of both tables:
You can easily notice that we have 2 new rows in the table country, one for Spain and one for Russia. Their ids are 6 and 7. Also notice, that in the city table there is no country_id with value 6 or 7. This simply means that we don’t have a city from Russia or Spain in our database. We’ll use this fact later.
INNER JOIN / CROSS JOIN
В некоторых SQL базах INNER JOIN не может идти без условия, но в MySQL это возможно, поэтому INNER JOIN и CROSS JOIN в данной SQL системе идентичны, как и JOIN, который является синонимом для INNER JOIN.
Простая выборка, без условий, подставит ко всем вариантам из левой таблицы, все варианты из правой таблицы (перекрестное соединение):
Тот же самый результат можно получить путем следующих записей, которые идентичны:
К выборке можно добавить условие, это актуально как для CROSS, так и для INNER JOIN. Выборку можно производить следующими способами:
- USING — если в условии участвуют столбцы с одинаковым названием. Не возможно использовать при перечислении таблиц через запятую.
- ON — если сопоставляются столбцы с разным названием. Фильтрация этой командой происходит до того как сопостовляются строки таблицы. Не возможно использовать при перечислении таблиц через запятую.
- WHERE — если сопоставляются столбцы с разным названием. Фильтрация этой командой происходит после того как сопостовляются строки таблицы. Можно использовать при перечислении через запятую. Список возможных условий.
В таблице ниже, сопоставилены строки из разных таблиц, но имеющие одинаковый id. В этом случае для BMW и зеленого цвета пары не нашлось, и они не попали в результирующую таблицу:
Ту же самую таблицу можно получить следущими записями:
Если бы столбец id у таблицы с цветами назывался бы color_id, то запись для ON и WHERE была бы следующей:
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the «Customers» table:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
And a selection from the «Orders» table:
OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
---|---|---|---|---|
10308 | 2 | 7 | 1996-09-18 | 3 |
10309 | 37 | 3 | 1996-09-19 | 1 |
10310 | 77 | 8 | 1996-09-20 | 2 |
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Let’s look at a selection from the «Orders» table:
OrderID | CustomerID | OrderDate |
---|---|---|
10308 | 2 | 1996-09-18 |
10309 | 37 | 1996-09-19 |
10310 | 77 | 1996-09-20 |
Then, look at a selection from the «Customers» table:
CustomerID | CustomerName | ContactName | Country |
---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mexico |
Notice that the «CustomerID» column in the «Orders» table refers to the «CustomerID» in the «Customers» table. The relationship between the two tables above is the «CustomerID» column.
Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDateFROM OrdersINNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
and it will produce something like this:
OrderID | CustomerName | OrderDate |
---|---|---|
10308 | Ana Trujillo Emparedados y helados | 9/18/1996 |
10365 | Antonio Moreno Taquería | 11/27/1996 |
10383 | Around the Horn | 12/16/1996 |
10355 | Around the Horn | 11/15/1996 |
10278 | Berglunds snabbköp | 8/12/1996 |
LEFT OUTER JOIN
results in rows as The General Idea/:
results in rows as The General Idea/:
The troubles with the Venn diagram
An image internet search on «sql join cross inner outer» will show a multitude of Venn diagrams. I used to have a printed copy of one on my desk. But there are issues with the representation.
Venn diagram are excellent for set theory, where an element can be in one or both sets. But for databases, an element in one «set» seem, to me, to be a row in a table, and therefore not also present in any other tables. There is no such thing as one row present in multiple tables. A row is unique to the table.
Self joins are a corner case where each element is in fact the same in both sets. But it’s still not free of any of the issues below.
The set represents the set on the left (the table) and the set is the set on the right (the table) in below discussion.
CROSS JOIN
Every element in both sets are matched with every element in the other set, meaning we need amount of every elements and amount of every elements to properly represent this Cartesian product. Set theory isn’t made for multiple identical elements in a set, so I find Venn diagrams to properly represent it impractical/impossible. It doesn’t seem that fits at all.
The rows are distinct. The is 7 rows in total. But they’re incompatible for a common results set. And this is not how a works at all:
Trying to represent it like this:
..but now it just looks like an , which it’s certainly not. Furthermore there’s no element in the that is actually in any of the two distinct sets. However, it looks very much like the searchable results similar to this:
For reference one searchable result for s can be seen at Tutorialgateway. The , just like this one, is empty.
INNER JOIN
The value of an element depends on the condition. It’s possible to represent this under the condition that every row becomes unique to that condition. Meaning is only true for one row. Once a row in table () matches multiple rows in table () under the condition, the result has the same problems as the : The row needs to be represented multiple times, and the set theory isn’t really made for that. Under the condition of uniqueness, the diagram could work though, but keep in mind that the condition determines the placement of an element in the diagram. Looking only at the values of the condition with the rest of the row just along for the ride:
This representation falls completely apart when using an with a condition making it into a .
With a self-, the rows are in fact idential elements in both tables, but representing the tables as both and isn’t very suitable. For example a common self- condition that makes an element in to be matching a different element in B is , making the match from to on seperate elements. From the examples that would be a like this:
Meaning Smith is the leader of both Green and Jensen.
OUTER JOIN
Again the troubles begin when one row has multiple matches to rows in the other table. This is further complicated because the can be though of as to match the empty set. But in set theory the union of any set and an empty set, is always just . The empty set adds nothing. The representation of this is usually just showing all of to illustrate that rows in are selected regardless of whether there is a match or not from . The «matching elements» however has the same problems as the illustration above. They depend on the condition. And the empty set seems to have wandered over to :
WHERE clause — making sense
Finding all rows from a with Smith and postalcode on the Moon:
Now the Venn diagram isn’t used to reflect the . It’s used only for the clause:
..and that makes sense.
When INTERSECT and UNION makes sense
INTERSECT
As explained an is not really an . However s can be used on results of seperate queries. Here a Venn diagram makes sense, because the elements from the seperate queries are in fact rows that either belonging to just one of the results or both. Intersect will obviously only return results where the row is present in both queries. This will result in the same row as the one above , and the Venn diagram will also be the same:
UNION
An is not a . However work under the same conditions as , resulting in a return of all results combining both s:
which is equivalent to:
..and gives the result:
Also here a Venn diagram makes sense:
When it doesn’t apply
An important note is that these only work when the structure of the results from the two SELECT’s are the same, enabling a comparison or union. The results of these two will not enable that:
..trying to combine the results with gives a
Сложные и многотабличные запросы
В примере для простоты использовалось только 2 простых таблицы, в реальности же количество условий и таблиц может быть значительно больше. При необходимости таблицу так же можно объединять саму с собой, но в этом случае стоит использовать алиас для имени таблицы:
В 1 запросе можно использовать разные типы объединений, и разное количество запросов, но стоит учесть, что это значительно увеличивает время на обработку запроса. В случае если приходиться использовать длинные запросы, возможно стоит подумать над правильностью организации архитектуры таблиц.
Объединения таблиц можно использовать не только в выборке, но и при удалении и изменении таблиц (UPDATE, DELETE).
tnx
SQL References
SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE
MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER
Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE
Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK
Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION
SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER
Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN
Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR
Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME
MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase
Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val
Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year
Other Functions CurrentUser Environ IsDate IsNull IsNumeric
SQL OperatorsSQL Data TypesSQL Quick Ref
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the «Orders» table:
OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
---|---|---|---|---|
10308 | 2 | 7 | 1996-09-18 | 3 |
10309 | 37 | 3 | 1996-09-19 | 1 |
10310 | 77 | 8 | 1996-09-20 | 2 |
And a selection from the «Customers» table:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
SQL INNER JOIN for all columns
To get all the columns from foods and company table after joining, 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:
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
Difference between JOIN and INNER JOIN
JOIN returns all rows from tables where the key record of one table is equal to the key records of another table.
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.
An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same for both the students and courses tables.
Using JOIN Clause
Using INNER JOIN Clause
What is Inner Join in SQL?
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.
Syntax:
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;
Pictorial Presentation:
The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.
Syntax diagram — INNER JOIN
Example: SQL INNER JOIN between two tables
Here is an example of inner join in SQL between two tables.
Sample table: foods
Sample table: company
To join item name, item unit columns from foods table and company name, company city columns from company table, with the following condition —
1. company_id of foods and company table must be same,
the following SQL statement can be used :
SQL Code:
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:
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:
SQL JOIN Examples
Problem: List all orders with customer information
SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country FROM JOIN Customer ON .CustomerId = Customer.Id
table Aliases Result: 830 records.
OrderNumber | TotalAmount | FirstName | LastName | City | Country |
---|---|---|---|---|---|
542378 | 440.00 | Paul | Henriot | Reims | France |
542379 | 1863.40 | Karin | Josephs | Münster | Germany |
542380 | 1813.00 | Mario | Pontes | Rio de Janeiro | Brazil |
542381 | 670.80 | Mary | Saveley | Lyon | France |
542382 | 3730.00 | Pascale | Cartrain | Charleroi | Belgium |
542383 | 1444.80 | Mario | Pontes | Rio de Janeiro | Brazil |
542384 | 625.20 | Yang | Wang | Bern | Switzerland |
PRODUCT |
---|
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
ORDERITEM |
---|
Id |
OrderId |
ProductId |
UnitPrice |
Quantity |
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
Problem: List all orders with product names, quantities, and prices
SELECT O.OrderNumber, CONVERT(date,O.OrderDate) AS Date, P.ProductName, I.Quantity, I.UnitPrice FROM O JOIN OrderItem I ON O.Id = I.OrderId JOIN Product P ON P.Id = I.ProductId ORDER BY O.OrderNumber
This query performs two JOIN operations with 3 tables. The O, I, and P are table Aliases. Date is a column Alias.
Result: 2155 records
OrderNumber | Date | ProductName | Quantity | UnitPrice |
---|---|---|---|---|
542378 | 7/4/2012 12:00:00 AM | Queso Cabrales | 12 | 14.00 |
542378 | 7/4/2012 12:00:00 AM | Singaporean Hokkien Fried Mee | 10 | 9.80 |
542378 | 7/4/2012 12:00:00 AM | Mozzarella di Giovanni | 5 | 34.80 |
542379 | 7/5/2012 12:00:00 AM | Tofu | 9 | 18.60 |
542379 | 7/5/2012 12:00:00 AM | Manjimup Dried Apples | 40 | 42.40 |
542380 | 7/8/2012 12:00:00 AM | Jack’s New England Clam Chowder | 10 | 7.70 |
542380 | 7/8/2012 12:00:00 AM | Manjimup Dried Apples | 35 | 42.40 |
542380 | 7/8/2012 12:00:00 AM | Louisiana Fiery Hot Pepper Sauce | 15 | 16.80 |
542381 | 7/8/2012 12:00:00 AM | Gustaf’s Knäckebröd | 6 | 16.80 |
542381 | 7/8/2012 12:00:00 AM | Ravioli Angelo | 15 | 15.60 |
542381 | 7/8/2012 12:00:00 AM | Louisiana Fiery Hot Pepper Sauce | 20 | 16.80 |
542382 | 7/9/2012 12:00:00 AM | Sir Rodney’s Marmalade | 40 | 64.80 |
542382 | 7/9/2012 12:00:00 AM | Geitost | 25 | 2.00 |
Previous
Next
С этим читают
- Работа с датой и временем в mysql
- Оператор between (transact-sql)between (transact-sql)
- Обновление sql server с помощью мастера установки (программа установки)upgrade sql server using the installation wizard (setup)
- Group by and having clause in sql
- Identity (функция) (transact-sql)identity (function) (transact-sql)
- Таблицы oracle
- Команда union
- Select distinct from (dmx)
- Sql server: alter table statement
- Удаление базы данныхdelete a database