Sql left join

INNER JOIN

Let’s discuss these two queries:

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:

And the orders table with the following data:

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:

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:

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:

And a selection from the «Orders» table:

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:

Then, look at a selection from the «Customers» table:

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:

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:

And a selection from the «Customers» table:

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.

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

  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