JOINS

JOINS

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft® SQL Server™ 2000 should use data from one table to select the rows in another table.

A join condition defines the way two tables are related in a query by:

• Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
• Specifying a logical operator (=, <>, and so on) to be used in comparing values from the columns.

Joins can be specified in either the FROM or WHERE clauses. The join conditions combine with the WHERE and HAVING search conditions to control the rows that are selected from the base tables referenced in the FROM clause.

Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins. A simplified SQL-92 FROM clause join syntax is:

	SELECT columnList FROM first_table join_type second_table [ON (join_condition)]

 

Join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition defines the predicate to be evaluated for each pair of joined rows. This is an example of a FROM clause join specification:

Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates

When SQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join. Although the physical execution of various joins uses many different optimizations, the logical sequence is:

• The join conditions in the FROM clause are applied.
• The join conditions and search conditions from the WHERE clause are applied.
• The search conditions from the HAVING clause are applied.

Types of Joins

Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.

Joins can be categorized as:

• Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.

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 in both the students and courses tables.

• Outer joins. Outer joins can be a left, a right, or full outer join.

Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

• LEFT JOIN or LEFT OUTER JOIN

The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

• RIGHT JOIN or RIGHT OUTER JOIN.

A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

• FULL JOIN or FULL OUTER JOIN.

A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

• Cross joins.

Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.

Case Study:

When you look at the example tables below, notice that: 

• The "Employee_ID" column is the primary key of the "Employees" table
• The "Prod_ID" column is the primary key of the "Orders" table
• The "Employee_ID" column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names

---

Employees:

Employee_ID	Name
01	Hansen, Ola
02	Svendson, Tove
03	Svendson, Stephen
04	Pettersen, Kari

Orders:

Prod_ID	Product	Employee_ID
234	Printer	01
657	Table	03
865	Chair	03

---

 

Using Joins

Example INNER JOIN

Syntax

SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

Who has ordered a product, and what did they order?

SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
Result

Name	Product
Hansen, Ola	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Example LEFT JOIN

Syntax

SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

List all employees, and their orders - if any.

SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
Result

Name	Product
Hansen, Ola	Printer
Svendson, Tove
Svendson, Stephen	Table
Svendson, Stephen	Chair
Pettersen, Kari

 

Example RIGHT JOIN

Syntax

SELECT field1, field2, field3 FROM first_table RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

List all orders, and who has ordered - if any.

SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
Result

Name	Product
Hansen, Ola	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Example

Who ordered a printer?

SELECT Employees.Name FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID WHERE Orders.Product = 'Printer'

Result

Name

Hansen, Ola

Self Join:

          Joining a table to it self is called as self join. In a self-join, we have to use two copies of the same table twice. To distinguish between the two copies we have to use duplicate names to the tables called as table aliases.

Example:

         

          Get the employees and corresponding manager names from the following emp table. In this table mgrid filed refers the id of the manager for the employee. These are nothing but empid’s. It means, any one of the employees will become the manager for other employee.

EMP
Empid	Empname	mgrid
100	Anil	104
101	Balu	103
102	Santosh	100
103	Vivek	104
104	Jagan	102

	SELECT a.empid,a.empname,a.mgrid,b.empname ManagerName FROM EMP a INNER JOIN          EMP b ON a.mgrid=b.empid

 

BUILT IN FUNCTIONS

          SQL Server 2000 provides a lot of functions, which can be used as calculated fields as part of column lists in a SELECT statement. Such functions are called as Built-in Functions.

Arithmetic operators

The arithmetic operators supported by SQL server are:

Ø  + for addition

Ø  - for subtraction

Ø  / for division

Ø  * for multiplication

Ø  % for modulo

The modulo arithmetic operator is used to obtain the remainder of two divisible numeric integer values. It cannot be used with money data type columns.

All the arithmetic operators can be used in the SELECT list with the column names and numeric constants in a combination.

Example:

	SELECT title_id, price, price+5, ytd_sales*5 FROM titles.

 

When any arithmetic operation is performed on a NULL value, the result is always NULL because NULL values have no explicitly assigned values. Arithmetic operations can be performed on more than one column at a time. Consider the following query code:

	SELECT Title_Id=title_id, sValue=ytd_sales*price FROM tiles

 

The above query computes the product of ytd_sales and price from the titles table and displays the output with the user-defined headings.

Some rules regarding the usage of arithmetic operators are:

Ø  Arithmetic operations can be performed on numeric columns or numeric constants.

Ø  The modulo (%) operator cannot be used with columns of money, smallmoney, float or real datatypes.

The syntax is:

          SELECT function_name(parameters)

String functions provided by SQL server.

FUNCTION	DESCRIPTION
Expression+expression[+expression]	Concatenates two or more character or binary strings.
ASCII (character_expression)	Returns the ASCII value of the characater expression
CHAR (integer_expression)	Returns the character equivalent of the ASCII code value.

CHARINDEX(‘pattern’, expression)	Returns the starting position of the specified pattern.
DIFFERENCE (character_expression1, character_expression2)	Compares two straing and evaluates the similarity between them on a scale of 1 to 4.
LOWER (character_expression)	Converts two string and evaluates the similarity between them on a scale of 1 to 4.
LTRIM(character_expression)	Returns the data with out leading brackets
PATINDEX(‘%pattern’, expression)	Returns the starting position of the first occurrence of the pattern in the specified expression, zero if the pattern is not found.
REPLICATE(char_expression, integer_expression	Repeats a character expression a specified number of times.
REVERSE(character_expression)	Returns the reverse of character expression.
RIGHT(character_expression, integer_expression)	Returns the part of the character string from the right.
RTRIM(character_expression)	Returns the data without tailing blanks.
SOUNDEX(character_expression)	Returns the four-digit code to evaluate the similarity of two character strings.
SPACE(numeric_expression)	Returns a string of repeated spaces. The number of spaces is equal to the integer expression.
STR(float expression[,length[,decimal]])	Returns character data converted from numeric data.
STUFF(charactaer_expression1,start,length, character_expression)	Deletes length characters from first character exspression at start and then inserts character expression2 into character expression.
UPPER(character_expression)	Converts the character expression into upper case.

Date Functions:

            The syntax is:

          SELECT date_function(parameters)

FUNCTION	DESCRIPTION
DATEADD(datepart, number, date)	Adds the no.of dateparts to date.
DATEDIFF(datepart, date1, date2)	Returns the number of dateparts between two dates.
DATENAME(datepart, date)	Returns the integer value of the date part.
DATEPART(datepart,date)	Returns the integer value of the date part.
GETDATE()	Returns the current date and time.

Examples:

          SELECT GETDATE()

          The above statement displays the current system date and time with the help of the GETDATE function.

          SELECT DATEDIFF(yy,ord_date,getdate())

This statement uses the DATEDIFF function to find the difference between the current date and the order_date, from sales tables. The difference should be expressed in terms of number of years.

          SELECT title, DATEPART(yy,pubdate) FROM  titles.

The above statement uses the DATEPART function to return the year when the book was published, along with the title name.

SELECT Title=title_id, Month=dATENAME(mm,pubdate), year=DATENAME(yy, pubdatae) FROM titles.

Data Conversion:

           

SQL server handles certain datatype conversion automatically. If a character expression is compared with an int expression, SQL server makes the conversion automatically for the comparison(implicit conversion).

The CONVERT  function is used to change data from one type to another when SQL server cannot implicitly perform a conversion. Using the CONVERT function, data can be modified in variety of styles.

The syntax is:

CONVERT(datatype[(length), expression[,style])

         

	SELECT Ytd_Sales=CONVERT(char(10),ytd_sales) FROM titles. SELECT CONVERT(int, zip) FROM authors

 

Aggregate Functions

            Aggregate functions are used to produce summary data using tables.

Function	Parameters	Description
AVG	(ALL/DISTINCT] expression	Returns the average of values specified in the expression, either all records or distinct records
SUM	(ALL/DISTINCT] expression)	Returns the sum of values specified in the expression, either all records or distinct records.
MIN	(expression)	Returns the minimum of a value specified in the expression.
MAX	(expression)	Returns the maximum of a value specified in the expression.
COUNT	(ALL| DISTINCT expression)	Returns the number of unique or all records specified in an expression.
COUNT	(*)	Returns the total number of records specified in an expression.

Examples of Aggregate functions

SELECT ‘Average Price”=AVG(price) FROM titles	Returns the average value of all the price values in the titles table with user-defined heading.
SELECT ‘Sum’=SUM(DISTINCT advance) FROM titles	Returns the sum value of all-the unique advance values in the titles table with user-defined heading.
SELECT ‘Minimum Ytd Sales’=MIN(ytd_sales) FROM titles	Returns the minimum value of ytd_sales value in the titles table with user-defined heading.
SELECT ‘Maximum Ytd Sales’=MAX(ytd_sales) FROM titles	Returns the maximum value of ytd_sales in the titles table with user-defined heading.
SELECT ‘Unique Price’=COUNT(DISTINCT price) FROM titles	Returns the number of unique price values in the titles table with user-defined heading.
SELECT ‘Price=COUNT(price)  FROM titles	Returns the number of total number of price values in the titles with user-defined heading.

Selecting Rows

            There are situations in which only a few rows need to be retrieved from the table based on a condition. The WHERE clause, is provided by SQL server, to specify a condition.

The syntax for using the WHERE clause is:

SELECT column_list  FROM table_name WHERE search_condition

         

         

Example:

SELECT * FROM publishers WHERE  state = ‘MA’

         

Search Based On Conditions

           

            SQL Server provides few methods of searching rows in a table. These methods can be broadly categorized into the following categories.

Ø  Comparison operators like =, >, <, >=, <=, !=, !< and !>

Ø  Range operators like BETWEEN and NOT BETWEEN.

Ø  List operators line IN and NOT IN.

Ø  String operators like LIKE and NOT LIKE.

Ø  Unknown values like IS NULL and NOT NULL.

Ø  Logical operators like AND , OR and NOT.

Comparison Operator

            The command syntax is:

         

SELECT column_list FROM table_name WHERE expression1 comparison_operator expression2

Valid Comparison operators

Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or Equal to
<=	Less than or Equal to
<>, !=	Not Equal to
!>	Not Greater than
!<	Not Less than
()	Controls Precedence

Examples:    

	SELECT title FROM titles WHERE type=’business’ SELECT stor_id, ord_num, title_id FROM sales WHERE qty>20 SELECT type, title_id, price FROM titles WHERE price * ytd_sales<advance.

 

Range Operator

            The range operator is used to retrieve data that can be extracted in ranges. The range operations are:

Ø  BETWEEN

Ø  NOT BETWEEN

The syntax is:

	SELECT column_list FROM table_name WHERE expression1 range_operator expression2 AND expression2 Examples: 1. SELECT title from titles WHERE advance BETWEEN 2000 AND 5000 2. SELECT title FROM titles WHERE  advance NOT BETWEEN 4000 AND 5000

 

List Operator

            The syntax is:

	SELECT column_list  FROM table_name WHERE expression list_operator(‘value_list’) Examples: 1. SELECT pub_name, city, state FROM publishers WHERE state IN (‘MA’, ‘DC’) 2. SELECT pub_name, city, state FROM publishers WHERE state NOT IN (‘MA’, ‘DC’)

 

String Operator

           

SQL Server provides a pattern-matching method for string expressions using the LIKE keyword with the wildcard characters. The LIKE keyword is used to select those rows that match the specified portion of character string. The LIKE keyword allows wildcard characters that can be used as a part of an expression.

Wild card	Description
%	Represents any string of zero or more characters(s)
-	Represents a single character
[]	Represents any single character within the specified range.
[^]	Represents any single character not within the specified range.

Examples of the LIKE operator with wildcards.

Example	Description
SELECT title FROM titles WHERE type LIKE ‘bus%’	Returns all titles from titles table where first three characters of  the column type are ‘bus’
SELECT *  FROM publishers WHERE country LIKE ‘US_’	Returns all rows from publishers table where country name is three characters long and starts with  US where the third character can be anything.
SELECT title_id, price FROM titles WHERE title_id LIKE ‘P[SC]]%’	Returns all columns from the titles table where title_id starts with the character P and contains S or C in the second position followed by any number of characters.
SELECT title_id, price FROM titles WHERE title_id, LIKE ‘P[^C]%’	Returns all title_id and price from the titles table where title_id starts with P and does not contain and S as the second character and the third position onwards can contain any characters.

Unknown Values

            Unknown values refer to the data entered in the form of the  NULL keyword. In SQL serve terms, NULL is an unknown value or the value for which data is not available. The rows containing the NULL values can be retrieved by using the IS NULL keyword in the WHERE clause.

The Syntax is:

          SELECT column_list FROM table_name

          WHERE column_name unknown_value_operator.

          SELECT title, ytd_sales FROM titles WHERE ytd_sales IS NULL

Logical Operator

Ø  OR – Returns the result when any of the specified search conditions is true.

Ø  AND – returns the result when all of the specified search conditions are true.

Ø  NOT – Bnegates the expression that follows it.

SELECT column_list FROM  table_name

WHERE conditional_expression{AND\OR}[NOT] conditional_expression.

Examples of Logical operators

Example	Description
SELECT * FROM publishers WHERE city=’Boston’ OR city=’Paris’	Returns all the rows specific to the conditions, even if any one of the conditions is true.
SELECT publishers WHERE city=’Boston’ AND city=’MA’	Returns all the rows specific to the conditions, when both the conditions are true.
SELECT * FROM  publishers WHERE city=’Boston’ OR NOT  city=’Paris’	Returns all the rows specific to the conditions, except the rows specified with the condition after NOT operator.

Distinct

            The distinct clause removes duplicate rows from the result set. Duplicate rows can be eliminated by using the DISTINCT keyword in the SELECT statement.

Syntax:

SELECT [ALL|DISTINCT] column_name           FROM table_name           WHERE search_condition Example:           SELECT DISTINCT title_id           FROM titleauthor           WHERE title_id LIKE’PS%’

           

TOP and PERCENT

            The TOP clause limits the number of rows returned in the result set.

          The syntax is:

          TOP n [PERCENT]

Where n specifies the number of rows are to be returned, if PERCENT is not specified. If PERCENT is specified, n specifies the percentage of the rows to be returned.

The TOP clause is used with the SELECT statement.

The following example returns the top 20 rows of the result set.

          SELECT TOP 20

The next example returns the 10% of rows from top of the result set.

          SELECT TOP 10 PERCENT

It the SELECT statement, including TOP, has  and ORDER BY  clause, then the rows to be returned are selected after the ORDER BY statement has been applied.