SQL LEFT JOIN Keyword

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL LEFT JOIN Syntax

[php]
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name
[/php]

or:

[php]
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name
[/php]

<b>PS!</b> In some databases LEFT JOIN is called LEFT OUTER JOIN.

[php]
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
[/php]

Note: The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).

 

SQL INNER JOIN Keyword

SQL INNER JOIN Keyword

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SQL INNER JOIN Syntax

[php]
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
[/php]

or:

[php]
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
[/php]

PS! INNER JOIN is the same as JOIN.

SQL INNER JOIN

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 INNER JOIN Example

The following SQL statement will return all customers with orders:

Example

[php]
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
[/php]

Tip: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the “Customers” table that do not have matches in “Orders”, these customers will NOT be listed.

SQL Joins

SQL joins are used to combine rows from two or more tables.

SQL JOIN

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

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, have a 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, if we run the following SQL statement (that contains an INNER JOIN):

Example

[php]
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
[/php]

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

Different SQL JOINs

Before we continue with examples, we will list the types of the different SQL JOINs you can use:

  • INNER JOIN: Returns all rows when there is at least one match in BOTH tables
  • LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
  • RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
  • FULL JOIN: Return all rows when there is a match in ONE of the tables

SQL Aliases

SQL aliases are used to temporarily rename a table or a column heading.

SQL Aliases

SQL aliases are used to give a database table, or a column in a table, a temporary name.

Basically aliases are created to make column names more readable.

SQL Alias Syntax for Columns

[php]
SELECT column_name AS alias_name
FROM table_name;
[/php]

SQL Alias Syntax for Tables

[php]
SELECT column_name(s)
FROM table_name AS alias_name;
[/php]

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
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
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

And a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10354 58 8 1996-11-14 3
10355 4 6 1996-11-15 1
10356 86 6 1996-11-18 2

Alias Example for Table Columns

The following SQL statement specifies two aliases, one for the CustomerName column and one for the ContactName column. Tip: It requires double quotation marks or square brackets if the column name contains spaces:

Example

[php]
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
[/php]

In the following SQL statement we combine four columns (Address, City, PostalCode, and Country) and create an alias named “Address”:

Example

[php]
SELECT CustomerName, Address+’, ‘+City+’, ‘+PostalCode+’, ‘+Country AS Address
FROM Customers;
[/php]

Note: To get the SQL statement above to work in MySQL use the following:

[php]
SELECT CustomerName, CONCAT(Address,’, ‘,City,’, ‘,PostalCode,’, ‘,Country) AS Address
FROM Customers;
[/php]

Alias Example for Tables

The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the “Customers” and “Orders” tables, and give them the table aliases of “c” and “o” respectively (Here we have used aliases to make the SQL shorter):

Example

[php]
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;
[/php]

The same SQL statement without aliases:

Example

[php]SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;
[/php]

Aliases can be useful when:

  • There are more than one table involved in a query
  • Functions are used in the query
  • Column names are big or not very readable
  • Two or more columns are combined together

SQL BETWEEN Operator

The BETWEEN operator is used to select values within a range.

The SQL BETWEEN Operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

[php]
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
[/php]

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Products” table:

ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 – 12 oz bottles 19
3 Aniseed Syrup 1 2 12 – 550 ml bottles 10
4 Chef Anton’s Cajun Seasoning 1 2 48 – 6 oz jars 22
5 Chef Anton’s Gumbo Mix 1 2 36 boxes 21.35

BETWEEN Operator Example

The following SQL statement selects all products with a price BETWEEN 10 and 20:

Example

[php]
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
[/php]

NOT BETWEEN Operator Example

To display the products outside the range of the previous example, use NOT BETWEEN:

Example

[php]
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
[/php]

BETWEEN Operator with IN Example

The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:

Example

[php]
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);
[/php]

BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter BETWEEN ‘C’ and ‘M’:

Example

[php]
SELECT * FROM Products
WHERE ProductName BETWEEN ‘C’ AND ‘M’;
[/php]

NOT BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN ‘C’ and ‘M’:

Example

[php]SELECT * FROM Products
WHERE ProductName NOT BETWEEN ‘C’ AND ‘M’;
[/php]

Sample Table

Below is a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 7/4/1996 3
10249 81 6 7/5/1996 1
10250 34 4 7/8/1996 2
10251 84 3 7/9/1996 1
10252 76 4 7/10/1996 2

BETWEEN Operator with Date Value Example

The following SQL statement selects all orders with an OrderDate BETWEEN ’04-July-1996′ and ’09-July-1996′:

Example

[php]
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
[/php]

Notice that the BETWEEN operator can produce different result in different databases!
In some databases, BETWEEN selects fields that are between and excluding the test values.
In other databases, BETWEEN selects fields that are between and including the test values.
And in other databases, BETWEEN selects fields between the test values, including the first test value and excluding the last test value.Therefore: Check how your database treats the BETWEEN operator!

SQL IN Operator

The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

[php]
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,…);
[/php]

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
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

IN Operator Example

The following SQL statement selects all customers with a City of “Paris” or “London”:

Example

[php]
SELECT * FROM Customers
WHERE City IN (‘Paris’,’London’);
[/php]

 

SQL Wildcards

A wildcard character can be used to substitute for any other character(s) in a string.

SQL Wildcard Characters

In SQL, wildcard characters are used with the SQL LIKE operator.

SQL wildcards are used to search for data within a table.

With SQL, the wildcards are:

Wildcard Description
% A substitute for zero or more characters
_ A substitute for a single character
[charlist] Sets and ranges of characters to match
[^charlist]
or
[!charlist]
Matches only a character NOT specified within the brackets

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
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

Using the SQL % Wildcard

The following SQL statement selects all customers with a City starting with “ber”:

Example

[php]
<div class="w3-code notranslate">SELECT * FROM Customers
WHERE City LIKE ‘ber%’;</div>
[/php]

The following SQL statement selects all customers with a City containing the pattern “es”:

Example

[php]
<div class="w3-code notranslate">SELECT * FROM Customers
WHERE City LIKE ‘%es%’;</div>
[/php]

Using the SQL _ Wildcard

The following SQL statement selects all customers with a City starting with any character, followed by “erlin”:

Example

[php]
<div class="w3-code notranslate">SELECT * FROM Customers
WHERE City LIKE ‘_erlin’;</div>
[/php]

The following SQL statement selects all customers with a City starting with “L”, followed by any character, followed by “n”, followed by any character, followed by “on”:

Example

[php]
<div class="w3-code notranslate">SELECT * FROM Customers
WHERE City LIKE ‘L_n_on’;</div>
[/php]

Using the SQL [charlist] Wildcard

The following SQL statement selects all customers with a City starting with “b”, “s”, or “p”:

Example

[php]
<div class="w3-code notranslate">SELECT * FROM Customers
WHERE City LIKE ‘[bsp]%’;</div>
[/php]

The following SQL statement selects all customers with a City starting with “a”, “b”, or “c”:

Example

[php]
<div class="w3-code notranslate">SELECT * FROM Customers
WHERE City LIKE ‘[a-c]%’;</div>
[/php]

The following SQL statement selects all customers with a City NOT starting with “b”, “s”, or “p”:

Example

[php]
<div class="w3-code notranslate">SELECT * FROM Customers
WHERE City LIKE ‘[!bsp]%’;</div>
<div class="w3-code notranslate">[/php]

or

[php]
SELECT * FROM Customers
WHERE City NOT LIKE ‘[bsp]%’;</div>
<div class="w3-code notranslate">[/php]