Article Index
SQL Tutorial
Microsoft SQL Server
Stored In The Master Database?
Installing A Production Data Server
SQL Server B (Middle Of The Road)
SQL Commands
SQL BETWEEN
SQL Aggregate Functions
SQL ALIAS
SQL Subquery
SQL INTERSECT
Table Manipulation
SQL CREATE INDEX Statement
SQL PRIMARY KEY
SQL UPDATE Statement
Advanced SQL
SQL Running Totals

Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both statements).

The syntax is as follows:
[SQL Statement 1]
INTERSECT
[SQL Statement 2]

Let's assume that we have the following two tables,

Table Store_Information

Table Internet_Sales

and we want to find out all the dates where there are both store sales and internet sales. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales

Result:

Please note that the INTERSECT command will only return distinct values.

The syntax is as follows:

[SQL Statement 1]
MINUS
[SQL Statement 2]

Let's continue with the same example:

Table Store_Information

Table Internet_Sales

and we want to find out all the dates where there are store sales, but no internet sales. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales

Result:

"Jan-05-1999", "Jan-07-1999", and "Jan-08-1999" are the distinct values returned from "SELECT Date FROM Store_Information." "Jan-07-1999" is also returned from the second SQL statement, "SELECT Date FROM Internet_Sales," so it is excluded from the final result set.
Please note that the MINUS command will only return distinct values.


 SQL CONCATENATE

Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this:

·    MySQL: CONCAT()
·    Oracle: CONCAT(), ||
·    SQL Server: +

The syntax for CONCAT() is as follows:

CONCAT(str1, str2, str3, ...): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.

Let's look at some examples. Assume we have the following table:

Table Geography

Example 1:

MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = 'Boston';

Result:

'EastBoston'

Example 2:

Oracle:
SELECT region_name || ' ' || store_name FROM Geography
WHERE store_name = 'Boston';

Result:

'East Boston'

Example 3:

SQL Server:
SELECT region_name + ' ' + store_name FROM Geography
WHERE store_name = 'Boston';

Result:

'East Boston'

 SQL SUBSTRING

The Substring function in SQL is used to grab a portion of the stored data. This function is called differently for the different databases:

·    MySQL: SUBSTR(), SUBSTRING()
·    Oracle: SUBSTR()
·    SQL Server: SUBSTRING()

The most frequent uses are as follows (we will use SUBSTR() here):

SUBSTR(str,pos): Select all characters from <str> starting with position <pos>. Note that this syntax is not supported in SQL Server.

SUBSTR(str,pos,len): Starting with the <pos>th character in string <str> and select the next <len> characters.

Assume we have the following table:

Table Geography

Example 1:

SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';

Result:

's Angeles'

Example 2:

SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';

Result:

'an D'

 SQL TRIM

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces. This function is called differently in different databases:

·    MySQL: TRIM(), RTRIM(), LTRIM()
·    Oracle: RTRIM(), LTRIM()
·    SQL Server: RTRIM(), LTRIM()

The syntax for these trim functions are:

TRIM([[LOCATION] [remstr] FROM ] str): [LOCATION] can be either LEADING, TRAILING, or BOTH. This function gets rid of the [remstr] pattern from either the beginning of the string or the end of the string, or both. If no [remstr] is specified, white spaces are removed.

LTRIM(str): Removes all white spaces from the beginning of the string.

RTRIM(str): Removes all white spaces at the end of the string.

Example 1:

SELECT TRIM('   Sample   ');

Result:

'Sample'

Example 2:

SELECT LTRIM('   Sample   ');

Result:

'Sample   '

Example 3:

SELECT RTRIM('   Sample   ');

Result:
'   Sample'