MySQL Full Tutorial-Part3

1 2 3

SELECT DISTINCT lastname FROM employees ORDER BY lastname

The duplicate last names are eliminated in the result set when we use the DISTINCT operator.

MySQL DISTINCT and NULL values

If a column has NULL values and you use the DISTINCT operator for that column, MySQL will keep one NULL value and eliminate the other because the DISTINCT operator treats all NULL values as the same value.

For example, in the customers table, we have many rows with state column has NULL values. When we use the DISTINCT operator to query states of customers, we will see distinct states plus a NULL value as the following query:

1 2	SELECT DISTINCT state FROM customers

MySQL DISTINCT with multiple columns

You can use the DISTINCT operator with more than one column. The combination of all columns will be used to define the uniqueness of the row in the result set.

For example, to get the unique combination of city and state from the customers table, you use the following query:

1 2 3 4

SELECT DISTINCT state, city FROM customers WHERE state IS NOT NULL ORDER BY state, city

Without the DISTINCT operator, you will get duplicate combination state and city as follows:

1 2 3 4

SELECT state, city FROM customers WHERE state IS NOT NULL ORDER BY state, city

DISTINCT vs. GROUP BY Clause

If you use the GROUP BY clause in the SELECT statement without using aggregate functions, theGROUP BY clause will behave like the DISTINCT operator. The following queries produce the same result set:

1 2 3 4 5 6

SELECT DISTINCT state FROM customers; SELECT state FROM customers GROUP BY state;

The difference between DISTINCT operator and GROUP BY clause is that the GROUP BY clause sorts the result set whereas the DISTINCT operator does not.

MySQL DISTINCT and COUNT aggregate function

The DISTINCT operator is used with the COUNT function to count unique records in a table. In this case, it ignores the NULL values. For example, to count the unique states of customers in the U.S., you use the following query:

1 2 3

SELECT COUNT(DISTINCT state) FROM customers WHERE country = 'USA';

In this tutorial, we have shown you various ways of using MySQL DISTINCT operator such as eliminating duplicate records and counting non-NULL values.

Using MySQL LIMIT

Summary: in this tutorial, you will learn how to use MySQL LIMIT clause to select records from the beginning, middle and end of a result set.

MySQL LIMIT syntax

The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integer constants.

The following illustrates the LIMIT clause syntax with 2 arguments:

1 2	SELECT * FROM tbl LIMIT offset, count

Let’s see what the offset and count mean in the LIMIT clause:

• The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
• The count specifies maximum number of rows to return.

When you use LIMIT with one argument, this argument will be used to specifies the maximum number of rows to return from the beginning of the result set.

1 2	SELECT * FROM tbl LIMIT count

The query above is equivalent to the following query with the LIMIT clause that accepts two arguments:

1 2	SELECT * FROM tbl LIMIT 0, count

Using MySQL LIMIT to get the first N rows

You can use the LIMIT clause to select the first N rows in a table as follows:

1 2	SELECT * FROM tbl LIMIT N

For example, to select the first 10 customers, you use the following query:

1 2 3 4 5

SELECT customernumber,        customername,        creditlimit FROM customers LIMIT 10;

Using MySQL LIMIT to get the highest and lowest values

The LIMIT clause often used with ORDER BY clause. First, you use the ORDER BY clause to sort the result set based on a certain criteria, and then you use LIMIT clause to find lowest or highest values.

For example, to select 5 customers who have the highest credit limit, you use the following query:

1 2 3 4 5 6

SELECT customernumber,        customername,        creditlimit FROM customers ORDER BY creditlimit DESC LIMIT 5;

And the following query returns 5 customers who have the lowest credit limit:

1 2 3 4 5 6

SELECT customernumber,        customername,        creditlimit FROM customers ORDER BY creditlimit ASC LIMIT 5;

Using MySQL LIMIT to get the N highest values

One of the toughest questions in MySQL is how to select the N highest values in a result set e.g., select the second most expensive product, which you cannot use MAX or MIN functions to answer. However, you can use MySQL LIMIT to answer those kinds of questions.

Let’s take a look at the products result set of the following query:

1 2 3 4

SELECT productName,        buyprice FROM products ORDER BY buyprice DESC;

Our task is to get the highlight product, which is the second most expensive product in the products result set. In order to do so, you use LIMIT clause to select 1 row from the second row as the following query: (notice that the offset starts from zero)

1 2 3 4 5

SELECT productName,        buyprice FROM products ORDER BY buyprice DESC LIMIT 1, 1

Querying Data with MySQL IN Operator

Summary: in this tutorial, you will learn how to use the MySQL IN operator that determines if a specified value matches any value a list or a subquery.

Introduction to the MySQL IN Operator

The IN operator allows you to determine if a specified value matches any one of a list or a subquery. The following illustrates the syntax of the IN operator.

1 2 3

SELECT column_list FROM table_name WHERE (expr|column) IN ('value1','value2',...)

In the query above:

• You can use a column or an expression ( expr) with the IN operator in the WHERE clause of the SELECT statement.
• The values in the list must be separated by a comma (,)
• The IN operator can also be used in the WHERE clause of other statements such as INSERT,UPDATE, DELETE, etc.

The IN operator returns 1 if the value of the  column or the result of the expr expression is equal to any value in the list, otherwise it returns 0.

When the values in the list are all constants:

• First, MySQL evaluates the values based on the type of the column or result of the  expr.
• Second, MySQL sorts the values.
• Third, MySQL searches for values using binary search algorithm which is very fast.

Therefore a query that uses the IN operator with a list of constants will perform very fast.

If the expr or any value in the list is NULL, the IN operator returns NULL.

You can combine the IN operator with the NOT operator to determine if a value does not match any value in a list or a subquery.

Let’s practice with some examples of using the IN operator.

MySQL IN examples

If you want to find out all offices which locates in the U.S. and France, you can use the IN operator as the following query:

1 2 3

SELECT officeCode, city, phone FROM offices WHERE country IN ('USA','France')

You can achieve the same result with the OR operator as the following query:

1 2 3

SELECT officeCode, city, phone FROM offices WHERE country = 'USA' OR country = 'France'

In case the list has many values, you have to construct a very long statement with multipleOR operators. Hence the IN operator allows you to shorten the query and make the query more readable.

To get offices that does not locate in USA and France, you can use NOT IN in the WHERE clause as follows:

1 2 3

SELECT officeCode, city, phone FROM offices WHERE country NOT IN ('USA','France')

MySQL IN with subquery

The IN operator is often used with a subquery. For example, if you want to find order whose total amount is greater than $60K, you can use the IN operator as the following  query:

1 2 3 4 5 6 7 8 9 10

SELECT orderNumber,        customerNumber,        status,        shippedDate FROM orders WHERE orderNumber IN (     SELECT orderNumber     FROM   orderDetails     GROUP BY orderNumber     HAVING SUM(quantityOrdered * priceEach) > 60000)

In this tutorial, we have shown you how to use MySQL IN operator to determine if a value matches any value in a list or a subquery.

MySQL BETWEEN Operator Explained

Summary: in this tutorial, you will learn how to use MySQL BETWEEN operator to specify a range to test.

Introduction to MySQL BETWEEN Operator

The BETWEEN operator allows you to specify a range to test. The following illustrates the syntax of theBETWEEN operator:

1	expr (NOT) BETWEEN begin_expr AND end_expr

In the expression above:

• All expressions: expr, begin_expr and end_expr must return values with the same data type.
• The BETWEEN operator returns 1 if the value of the expr  is greater than or equal to (>=) thevalue of begin_expr and less than or equal to (<= ) the value of end_expr, otherwise it returns 0.
• The NOT BETWEEN returns 1 if the value of expr  is less than (<) the value of begin_expror greater than the value of end_expr, otherwise it returns 0.
• If any expression above is NULL, the BETWEEN returns NULL.

The BETWEEN operator is typically used in the WHERE clause of SELECT, INSERT,UPDATE and DELETE statements.

MySQL BETWEEN examples

Let’s practice with some examples of using the BETWEEN operator.

MySQL BETWEEN with number examples

Suppose you want to find product whose buy price within the range of $90 and $100, you can use the BETWEEN operator as the following query:

1 2 3 4 5

SELECT productCode,        productName,        buyPrice FROM products WHERE buyPrice BETWEEN 90 AND 100

You can achieve the same result by using the greater than or equal ( >=) and less than or equal (<=) operators as the following query:

1 2 3 4 5

SELECT productCode,        productName,        buyPrice FROM products WHERE buyPrice >= 90 AND buyPrice <= 100

To find the product whose buy price is out of the range of $20 and $100, you use combine theBETWEEN operator with the NOT operator as follows:

1 2 3 4 5

SELECT productCode,        productName,        buyPrice FROM products WHERE buyPrice NOT BETWEEN 20 AND 100

The query above is equivalent to the following query that uses the comparison operators, greater thanoperator ( >) and less than operator ( <) and a logical operator OR.

1 2 3 4 5

SELECT productCode,        productName,        buyPrice FROM products WHERE buyPrice < 20 OR buyPrice > 100

MySQL BETWEEN with dates example

When you use the BETWEEN operator with date values, to get the best result, you should use theCAST function to explicitly convert the type of column or expression to the DATE type. For example, to get the orders whose required date is from 01/01/2003 to 01/31/2003, you use the following query:

1 2 3 4 5 6 7

SELECT orderNumber,        requiredDate,        status FROM orders WHERE requireddate BETWEEN CAST('2003-01-01' AS DATE) AND     CAST('2003-01-31' AS DATE)

In the query above, because the data type of the required date column is DATE so we used the CASTfunction to convert the literal strings ‘ 2003-01-01‘ and ‘ 2003-12-31‘ to the DATE data type.

In this tutorial, you have learned how to use the BETWEEN operator to test if a value falls within a range of values. You also learn how to combine the BETWEEN operator with the NOT operator to select data whose value that are not in a range of values.

Using MySQL LIKE Operator to Select Data Based On Patterns

Summary: in this tutorial, you will learn how to use MySQL LIKE operator to select data based on patterns.

The LIKE operator is commonly used to select data based on patterns. Using the LIKE operator in appropriate way is essential to increase the query performance.

The LIKE operator allows you to select data from a table based on a specified pattern. Therefore theLIKE operator is often used in the WHERE clause of the SELECT statement.

MySQL provides two wildcard characters for using with the LIKE operator, the percentage % and underscore _.

• The percentage ( %) wildcard allows you to match any string of zero or more characters.
• The underscore ( _) wildcard allows you to match any single character.

MySQL LIKE examples

Let’s practice with some examples of how to use the LIKE operator.

MySQL LIKE with percentage (%) wildcard

Suppose you want to search for employee whose first name starts with character ‘ a‘, you can use the percentage wildcard ( %) at the end of the pattern as follows:

1 2 3

SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%'

MySQL scans the whole employees table to find employee whose first name starts with character ‘a’ and followed by any number of characters.

To search for employee whose last name ends with ‘ on‘ string e.g., Patterson, Thompson, you can use the % wildcard at the beginning of the pattern as the following query:

1 2 3

SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on'

If you know the searched string is embedded inside in the column, you can use the percentage ( %) wildcard at the beginning and the end of the pattern. For example, to find all employees whose last names contain ‘ on‘ string, you can execute following query:

1 2 3

SELECT employeeNumber, lastName, firstName FROM employees WHERE lastname LIKE '%on%'

MySQL LIKE with underscore( _) wildcard

To find employee whose first name starts with T, ends with m and contains any single character between e.g., Tom, Tim, you use the underscore wildcard to construct the pattern as follows:

1 2 3

SELECT employeeNumber, lastName, firstName FROM employees WHERE firstname LIKE 'T_m'

MySQL LIKE operator with NOT operator

The MySQL allows you to combine the NOT operator with the LIKE operator to find string that does not match a specific pattern.

Suppose you want to search for employee whose last name does not start with character ‘ B‘, you can use the NOT LIKE with the pattern as the following query: