-->

Monday, September 22, 2014

How to use Constructs in Batches for Conditional Execution: SQL

SQL Server allows you to use programming constructs in the batches for conditional execution of statements. For example, you need to retrieve data based on a condition. If the condition is not satisfied, a message should be displayed.

The SQL Server allows you to use the following constructs to control the flow of statements:

  • IF…..ELSE statement
  • CASE statement
  • WHILE statement

Using the IF….ELSE Statement

You can use the IF…..ELSE statement for conditional execution of SQL statements. A particular action is performed when the given condition on evaluates to TRUE and another action is performed when the given condition evaluates to FALSE.
The syntax of IF….ELSE statement is:

IF Boolean_expression
{sql_statement | statement_block}
ELSE
{sql_statement | statement_block}]
Where,

  • Boolean_expression specifies the condition that evaluates to either TRUE or FALSE. Sql_statement specifies a T-SQL statement.
  • Statement_block is a collection of T-SQL statements.

The following example retrieves the pay rate of an employee from the EmployeePayHistory table to a variable, @Rate. The value of the @Rate variable is compared with the value 15 by using the <(less than) comparison operator. Based on the condition, different messages are displayed.

DECLARE @Rate money
SELECT @Rate = Rate FROM HumanResources.EmployeeHistory
WHERE EmployeeID = 23
IF @Rate < 15
PRINT ‘Review of the rate is required’
ELSE
BEGIN
PRINT ‘Review of the rate is not required’
PRINT ‘Rate =’
PRINT @Rate
END
GO

In the preceding example, the IF statement checks if the rate variable is storing a value less than 15. If the result is true, the PRINT statement displays “Review of the rate is required” else it displays “Review of the rate is not required”. Further, the next PRN statement displays the value of the rate.

Consider another example, where a check is performed to see the existence of the sales department. If the Sales department exists, all the details are displayed otherwise, a user-defined message is displayed.

IF EXISTS (SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’)
BEGIN
SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’
END
ELSE
PRINT ‘Department details not available’
GO

Saturday, September 20, 2014

Implementing Batches in SQL Server and Guidelines

As a database developer, you might need to execute more than one SQL statement to perform a task. For example, when a new employee joins AdventureWorks, Inc., you need to insert the employee details in the database. The details of the employees are stored in more than one table. Therefore, you need to execute an insert statement into store the details in each table. In such a case, you can send all the SQL statements together to the SQL Server to be executed as a unit. This helps in reducing the network traffic.

Creating Batches

A batch is a group of SQL statements submitted together to the SQL Server for execution. While executing batches, the SQL Server compiles the statements of a batch into a single executable unit called an execution plan. This helps in saving execution time.

Consider an example. You have to execute 10 statements and you are executing them one by one by sending 10 requests. This process takes time if your queries are in queue. All statements together in a batch, then the execution process becomes faster as all the statements are sent to the server together.

To create a batch, you can write multiple SQL statements followed by the keyword GO at the end, as shown in the following listing:
<T-SQL Statement1>
<T-SQL Statement2>
<T-SQL Statement3>
…. . .
GO

GO is a command that specifies the end of the batch and sends the SQL statements to an instance of the SQL Server.

Consider an example. If you want to store the details of new employees in the Adventure Works database, you can create the following batch:

INSERT INTO [AdventureWorks]. [Person]. [Contact]
VALUES (0, null, ‘Robert’, ‘J’ ‘Langdon’, NULL
,’rbl@adventure-works.com’, 0, ‘1 (11) 500 555-0172’
,’9E685955-ACD0-4218-AD7F-60DDF224C452’, ‘2a31OEw=’ , NULL
, newid( ), GETDATE ( ) )
INSERT INTO [AdventureWorks]. [HumanResources] . [Employee]
VALUES (‘AS01AS25R2E365W’, 19978, ‘robert1’, 16, ‘Tool Designer’,
‘1972-05-15’, ‘S’, ‘M’, ‘1996-07-31’, 0, 16, 20, 1, newid ( ) ,

When a batch is submitted to the SQL Server, it is compiled to create an execution plan. If any compilation error occurs, such as a syntax error, the execution plan is not created. Therefore, none of the statements in the batch is executed. However, after the execution plan is created, if a run-time error occurs, the execution of the batch stops. In such a case, the statements executed before the statement that encountered the run-time error are not affected.

Using Variables

While creating batches, you might need to save some values temporarily during the execution time. For example, you might need to store some intermediate values of calculations. To store the intermediate values, you can declare variables and assign values to them. You can declare a variable by using the DECLARE statement. The syntax of the DECLARE statement is:

DECLARE @variable_name data_type

Variables that are declare in a batch and can be used in any statement inside the batch are called local variables.
The following code declares a variable, @Rate, and assigns the maximum value of the Rate column from the EmployeePayHistory table to the variable:

DECLARE @Rate int
SELECT @Rate = max (Rate)
FROM HumanResources.EmployeePayHistory
GO
In the preceding example, the @Rate variable is declardd and used to store the maximum value of the Rate column. The max aggregate function is used to retrieve the maximum pay rate from the EmployeePayHistory table. The GO keyword is used to send all the statements together to the SQL Server.

Displaying User-Defined Messages

At times, you need to display user-defined messages or values of variables when the batch is executed. For this, you can use the PRINT statement, as shown in the following batch.
The following code displays the value of the rate variable by using the PRINT statement.

DECLARE @Rate int
SELECT @Rate = max (Rate)
FROM HumanResources.EmployeePayHistory
PRINT @Rate
GO
You can also use comment entries in batches to write a description of the code. This will help understand the purpose of the code. A comment entry can be written in two ways:

  • Multiple line comment entries enclosed within /* and */
  • Single line comment entry starting with – (double hyphens).

Guidelines to Create Batches

While crating batches, you need to consider the following guidelines:

  • You cannot combine statements, such as CREATE DEFAULT, CRATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW with other statements while creating a batch. Any statement that follows the create statement is interpreted as part of the definition.
  • You can use the EXECUTE statement in a batch when it is not the first statement of the batch, otherwise the EXECUTE statement works implicitly.
In addition, you need to consider the following restrictions:
  • You cannot bind rules and defaults to columns and use them in the same batch.
  • You cannot define and use the CHECK constraint in the same batch.
  • You cannot drop objects and recreate them in the same batch.
  • You cannot alter a table by adding a column and then refer to the new columns in the batch created earlier.


Wednesday, June 4, 2014

How to Create Index using sql Query: Sql Server

Database programmer should create indexes on the most frequently queried column in a table. However, at times, you might need to create an index based on a combination of one or more columns. An index based on one or more columns is called a composite index. A composite index can be based on a maximum of 16 columns. However, you need to consider that indexes with less number of columns use less disk space and involve fewer resources when compared to indexes based on more columns.

To create an index you can use the CREATE INDEX statement. The syntax of the CREATE INDEX statement is:

CRATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name} (column [ASC | DESC] [, …n])
[INCLUDE (column_name [, …n])]
[WITH (<relational_index_option>[, …n])]
[ON {partition_cheme_name (column_name [, …n]) | filegroup_name |DEFAULT) ]
<relation_index_option> : : =
{PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STASTISTICS_NO_RCOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}

Where,
  • UNIQUE crates an index where each row should contain a different index value. CLUSTERED specifies a clustered index where data is sorted on the index attribute. NONCLUSTERED specifies a nonclustered index that organizes data logically. The data is not sorted physically.
  • Index_name specifies the name of the index.
  • Table_name specifies the name of the table that contains the attributes on which the index is to be created.
  • Column specifies the column or columns on which the index will be created.
  • Column_name specifies the name of the column or columns on which the index would be created.
  • ON partition_scheme_name ( column_name ) specifies the partition scheme the specifies the filegroups in which the pattitioned index will be mapped.
  • ON filegroup_name specifies the filegroup on which index is created.
  • ON DEFAULT specifies that the specified index will be created on the default filegroup.
  • PAD_INDEX = { ON | OFF } specifies the index padding, which is OFF, by default.
  • FILLFACTOR = 1 to 100 specifies a percentage that indicates how full the leaf level of each index page should become during index creation or rebuild. The default value is 0.
  • SORT_IN_TEMPDB = { ON | OFF } specifies about storing temporary sort results in the tempdb.
  • IGNORE_DUP_KEY = { ON | OFF } specifies whether a duplicate key value can be inserted or not…
  • STATISTICS_NO_RECOMPUTE = { ON | OFF } specifies about recomputing the distribution statistics.
  • DROP_EXISTING = { ON | OFF } specifies that the pre-existing clustered, nonclustered, or XML index is dropped and rebuilt.
  • ONLINE = { ON | OFF } checks whether the underlying tables and associated indexes are available to query and modify the data during the index operation.

Developer can create online indexes only in the SQL Server 2005 Enterprise Edition.

Tuesday, February 18, 2014

How to use Correlated Subqueries in SQL Programming

In SQL programming, a correlated subquery can be defined as a query that depends on the outer query for its evaluation. In a correlated subquery, the WHERE clause references a table in the FROM clause. This means that the inner query is evaluated for each row of the table specified in the outer query.

For example, the following query displays the employee ID, designation, and number of hours spent on vacation for all the employees whose vacation hours are greater than the average vacation hours identified for their title:

SELECT BusinessEntityID, JobTitle, VacationHours
FROM HumanREsources.Employee el WHERE el.VacationHours>
(SELECT AVG(e2.VacationHours)
FROM HumanResources.Employee e2 WHERE el.JobTitle = e2.JobTitle)

In the preceding example, the inner query returns the Titles of the employees, from the Employee table, whose vacation hours are equal to the average vacation hours of all employees. The outer query retrieves the Employee ID, Title, and VacationHours of all the employees whose vacation hours is greater than the average vacation hours retrieved by the inner query. The output of the correlated subquery is shown in the following figure.

How to use Correlated Subqueries in SQL Programming

Thursday, February 13, 2014

How to use Nested Subqueries in SQL Programming

A subquery can contain one or more subqueries. Subqueries are used when the condition of a query is dependent on the result of another query, which in turn is dependent on the result of another subquery.

Consider an example. You need to view the DepartmentID of an employee whose e-mail address is Sales Agent. To perform this task, you can use the following query:

SELECT DepartmentID FROM HumanResources.EmployeeDepartmentHistory
WHERE BusinessEntityID = /* Level 1 inner query */
(SELECT BusinessEntityID FROM Person.BusinessEntityContact
WHERE ContactTypeID = /* Level 2 inner query */
(SELECT ContactTypeID FROM Person.ContactType WHERE Name= 'Sales Agent')
)

In the preceding example, two queries are nested within another query. The level 2 inner query returns the Contact ID of an employee based on the e-mail address of the employee from the Person table.

The level 1 inner query uses this ContactID to search for the BusinessEntityID of the employee with the given e-mail address. The main query uses the BusinessEntityID returned by level 1 inner query to search for the DepartmentID from the EmployeeDepartmentHistory table.

You can implement subqueries upto 32 levels. However, the number of levels that can be used depends on the memory available on the database server.

How to use Aggregate Functions in SubQuery with Database

While using subqueries, you can also use aggregate functions in the subqueries to generate aggregated values from the inner query. For example, in a manufacturing organization, the management wants to view the sale records of all the items whose sale records are higher than the average sale record of a particular product.

Therefore, the user first needs to obtain the average of a particular product and then find all the records whose sale record exceeds the average value. For this, you can use aggregate functions inside the subquery.

The following example displays the BusinessEntityID of those employees whose vacation hours are greater ta e average vacation hours of employees with title as ‘Marketing Assistant’:

SELECT BusinessEntityID FROM HumanResources.Employee
WHERE VacationHours > (SELECT AVG(VacationHours) FROM HumanResources.Employee
WHERE JobTitle = 'Marketing Assistant')

The output of the subquery that uses the aggregate function is shown in the following figure.

How to use Aggregate Functions in SubQuery with Database

In the preceding example, the inner query returns the average vacation hours of all the employees who are titled as Marketing Assistant. The outer query uses the comparison operator ‘>’ to retrieve the employee ID of all those employees who have vacation hours more than the average vacation hours assigned for a Marketing Assistant.

Thursday, February 6, 2014

How to Querying Data by using Subqueries in SQL Programming

In SQL programming, while using subqueries, you can use the =,>, and < comparison operators to create a condition that checks the value returned by the subquery. When a subquery returns more than one value, you might need to apply the operators to all the values returned by the subquery. To perform this task, you can modify the comparison operators in the subquery. The SQL Server provides the ALL and ANY keywords that can be used to modify the existing comparison operators.

The ALL keyword returns a TRUE value, if all the values that are retrieved by the subquery satisfy the comparison operator. It returns a FALSE value if only some values satisfy the comparison operator or if the subquery does not return any rows to the outer statement.

The ANY keyword returns a TRUE value if any value that is retrieved by the subquery satisfies the comparison operator. It returns a FALSE value if no values in the subquery satisfy the comparison operator or if the subquery does not return any rows to the outer statement.

The following list shows the operators that can be used with the ALL and ANY keywords:

  • >ALL, greater than the maximum value in the list.
    The expression|column_name> ALL (10, 20, 30) means ‘greater than 30’
  • >ANY, greater than the minimum value in the list.
    The expression|column_name >ANY (10, 20, 30) means ‘greater than 10’
  • =ANY, any of the values in the list. It acts in the same way as the IN clause.
    The expression|column_name = ANY (10, 20, 30) means ‘equal to either 10 or 20 or 30’
  • <>ANY, not equal to any value in the list.
    The expression|column_name <>ANY (10, 20, 30) means ‘not equal to 10 or 20 or 30’
  • <>ALL, not equal to all the values in the list. It cats in the same way as the NOT IN clause.
    The expression|column_name <>ALL (10, 20, 30) means ‘not equal to 10 and 20 and 30’

The following example displays the employee ID column and the title of all the employees whose vacation hours are more than the vacation hours of employees designated as Recruiter:

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE VacationHours >ALL (SELECT VacationHours
FROM HumanResources.Employee WHERE JobTitle = 'Recruiter')

In the preceding example, the inner query returns the vacation hours of all the employees who are titled as recruiter. The outer query uses the ‘>ALL’ comparison operator. This retrieves the details of those employees who have vacation hours greater than all the employees titled as recruiter.

The output of the query is displayed in the following figure.

How to Querying Data by using Subqueries in SQL Programming

Wednesday, February 5, 2014

How to Querying Data by using Subqueries in SQL Programming

IN

In SQL programming, if a subquery returns more than one value, you might need to execute the outer query if the values within the column specified in the condition match any value in the result set of the subquery. To perform this task, you need to use the IN keyword.
The syntax of using the IN keyword is:

SELECT column, column [,column]
FROM table_name
WHERE column [ NOT ] IN
(SELECT column FROM table_name [WHERE
Conditional_expression])

Consider an example. You need to retrieve the BusinessEntityID attribute of all the employees who live in Bothell, from the EmoloyeAddress table in the Adventure Works database.

To perform this task, you need to use a query to obtain the AddressID of all the addresses that contain the word Bothell. You can then obtain the BusinessEntityID from the Employee table where the AddresID matches any of the AddressIDs returned by the previous query.
To perform this task, you can use the following query:

SELECT BusinessEntityID FROM Person.BusinessEntityAddress
WHERE AddressID IN (SELECT AddressID FROM Person.Address WHERE City = 'Bothell')

The output of the subquery will show all the ID falls in the category.

EXISTS

You can also use a subquery to check if a set of records exist. For this, you need to use the EXISTS clause with a subquery. The EXISTS keyword, always returns a TRUE or FALSE value.
The EXISTS clause checks for the existence of rows according to the condition specified in the inner query and passes the existence status to the outer query. The subquery returns a TRUE value if the result of the subquery contains any row.

The query introduced with the EXISTS keyword differs from other queries. The EXISTS keyword is not preceded by any column name, constant, or other expression, and it contains an asterisk (*) in the SELECT list of the inner query. The syntax of the EXISTS keyword in the SELECT query is:

SELECT column, column [column]
FROM table_name
WHERE EXISTS (SELECT column FROM table_name [WHERE
Conditional_expression] )

Consider an example. The users of AdventureWorks, Inc. need a list containing the BusinessEntityID and Title of all the employees who have worked in the Marketing department at any point of time. The department ID of the Marketing department is 4.
To generate the required list, you can write the following query by using the EXISTS keyword:

SELECT BusinessEntityID, Title FROM HumanResources.Employee
WHERE EXISTS
(SELECT * FROM HumanResources.EmployeeDepartmentHistory WHERE
BusinessEntityID = HumanResources.Employee. BusinessEntityID AND DepartmentID = 4)

The following figure displays the output generated by the query.

How to Querying Data by using Subqueries in SQL Programming

A subquery must be enclosed within parentheses and cannot use the ORDER BY or the COMPUTE BY clause.

Tuesday, February 4, 2014

How to Querying Data by using Subqueries in SQL Programming: Part 1

In SQL programming, while querying data from multiple tables, you might need to use the result of one query as an input for the condition of another query. For example, in the Adventure Works database, you need to view the designation of all the employees who earn more than the average salary. In such cases, you can use subqueries to assign values to the expressions in other queries.

A subquery is an SQL statement that is used within another SQL statement. Subqueries are nested inside the WHERE or HAVING clause of the SELECT, INSERT, UPDATE, and DELETE statements. The query that represents the parent query is called an outer query, and the query that represents the subquery is called an inner query. The database engine executes the inner query first and returns the result to the outer query to calculate the result set.

Depending on the output generated by the subquery and the purpose for which it is to be used in the outer query, you can use different keywords, operators, and functions in subqueries.

Using the IN and EXISTS keywords

A subquery returns values that are used by the outer query. A subquery can return one or more values. Depending on the requirement, you can use these values in different ways in the outer query.

For example, in the Adventure Works database, you need to display the department name for an employee whose BusinessEntityID is 46. To perform this task, you can use the following query:

SELECT Name FROM HumanResources.Department
WHERE DepartmentID =
(SELECT DepartmentID FROM HumanResources.EmployeeDepartmentHistory WHERE BusinessEntityID = 46 AND EndDate IS NULL)

In the preceding query, the inner subquery returns the DepartmentID column of the emoloyee with BusinessEntityID as 46. Using this DepartmentID, the outer query returns the name of the department from the Department table i.e. Production.

In this query, EndDate is NULL Signifying that you need to extract the ID of the department where the emoloyee is currently working.

In the preceding example, the subquery returns a single value. However, at times, you need to return more than one value from the subquery. In addition, you might need to use a subquery only to check the existence of some records and based on that you need to execute the outer query.

You can specify different kinds of conditions on subqueries by using the following keywords:

Friday, January 31, 2014

How to Querying Data using Joins in Sql Programming: Part 4

Equi Join

In SQL programming, an Equi join is the same as an inner join and joins tables with the help of a foreign key. However, an equi join is used to display all the columns from both the tables. The common column from all the joining tables is displayed.

Consider an example where you apply an equi join between the EmployeeDepartmentHistory, Employee, and Department tables by using a common column, BusinessEntityID. To perform this task, you can use the following query:

SELECT * FROM HumanResources.EmployeeDepartmentHistory d
JOIN HumanResources.Employee e ON d.BusinessEntityID = e.BusinessEntityID
JOIN HumanResources.Department p ON p.DepartmentID = d.DepartmentID

The output of this query displays the EmployeeID column from all the tables, as shown in the following figure.

Equi join sql programming

Self Join

In a self-join, a table is joined with itself. As a result, one row in a table correlates with other rows in the same table. In a self-join, a table name is used twice in the query. Therefore, to differentiate the two instances of a single table, the table is given two alias names.

The following example performs a self-join of the Sales.SalesPerson table to produce a list of all the territories and the sales people working in them.

SELECT st.Name AS TerritoryName, sp.BusinessEntityID, sp.SalesQuota, sp.SalesYTD
FROM Sales.SalesPerson AS sp JOIN Sales.SalesTerritory AS st
ON sp.TerritoryID = st.TerritoryID
ORDER BY st.Name, sp.BusinessEntityID

The output of the self-join is shown in the following figure.

Self join sql programming


How to Querying Data using Joins in Sql Programming: Part 3

A cross join, also known as Cartesian Product in SQL programming, between two tables joins each row from one table with each row of the other table. The number of rows in the result set is the number of rows in the first table multiplied by the number of rows in the second table.

This implies that if Table A has 10 rows and Table B has 5 rows, then all 10 rows of Table A are joined with all 5 rows of Table B. therefore, the result set will contain 50 rows.

Consider following query in which all the BusinessEntityID is retrieved from SalesPerson table and SalesTerritory with their cross join. The where condition will filter the result according to the territoryID, and at the last the result will sort by the resulting BusinessEntityID.

SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
WHERE p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID

The preceding query combines the records of both the tables to display the result with all the possible combinations, as shown in the following figure.

How to Querying Data using Cross Joins in Sql Programming


Thursday, January 30, 2014

How to Querying Data using Joins in Sql Programming: Part 2

In comparison to an inner join, an outer join displays the result set containing all the rows from one table and the matching rows from another table. For example, if you create an outer join on Table A and Table B, it will show you all the records of Table A and only those records from Table B for which the condition on the common column holds true.

An outer join displays NULL for the columns of the related table where it does not find matching records. The syntax of applying an outer join is:

SELECT column_name, column_name [, column_name]
FROM table1_name [LEFT | RIGHT| FULL] OUTER JOIN table2_name
ON table_name.ref_column_name

An outer join is of three types:

Left Outer Join

A left outer join returns all rows from the table specified on the left side of the LEFT OUTER JOIN keyword and the matching rows from the table specified on the right side. The rows in the table specified on the left side for which matching rows are not found in the table specified on the right side, NULL values are displayed in the column that get data from the table specified on the right side.

Consider an example. The SpecialOfferProduct table contains a list of products that are on special offer. The SalesOrderDetail table stores the details of all the sales transactions. The users at AdventureWorks need to view the transaction details of these products. In addition, they want to view the ProductID of the special offer products for which no transaction has been done.

To perform this task, you can use the LEFT OUTER JOIN keyword, as shown in the following query:

Select p.ProductID, q.SalesOrderID, q.UnitPrice
From Sales.SpecialOfferProduct p
Left outer join Sales.SalesOrderDetail q on p.ProductID= q.ProductID
where SalesOrderID is NULL

The following figure displays the output of the preceding query.

How to Querying Data using Joins in Sql Programming: Part 2

Right Outer Join

A right outer join returns all the rows form the table specified on the right side of the RIGHT OUTER JOIN keyword and the matching rows from the table specified on the left side.

Consider the example of Adventure Works. Inc. The JobCandidate table stores the details of all the job candidates. You need to retrieve a list of all the job candidates. In addition, you need to find which candidate has been employed in Adventure Works, Inc. To perform this task, you can apply a right outer join between the Employee and JobCandidate tables, as shown in the following query:

SELECT e.JobTitle, d.JobCandidateID
FROM HumanResources.Employee e
RIGHT OUTER JOIN HumanResources.JobCandidate d
ON e.BusinessEntityID=d.BusinessEntityID

The result set displays the JobCandidateID column from the JobCandidate table and the BusinessEntityID column from the matching rows of the Employee table.

How to Querying Data using Joins in Sql Programming: Part 2

Full Outer Join

A full outer join is a combination of left outer join and right outer join. This join returns all the matching and non-matching rows from both the tables. However, the matching records are displayed only once. In case of non-matching rows, a NULL value is displayed for the columns for which data is not available.

SELECT e.BusinessEntityID, e.EmployeeName,ed.EmployeeEducationCode,
ed. Education
FROM Employee e FULL OUTER JOIN Education ed
ON e.EmployeeEducationCode = ed.EmployeeEducationCode

According to this query, the employee details and their highest educational qualification is displayed. For non-matching values, NULL is displayed.

Tuesday, January 28, 2014

How to Querying Data using Joins in Sql Programming

In a normalized database, the data to be viewed can be stored in multiple tables. When you need to view data from related tables together, you can query the data by joining the tables with the help of common attributes. You can also use subqueries where the result of a query is used as an input for the condition of another query.

This article discusses about how to query data from multiple tables by applying various types of joins, such as an inner join, outer join, cross join, equijoin, or self-join.

Querying Data by Using Joins

As a database developer, you may need to retrieve data from more than one table together as a part of a single result set. In such a case, different columns in the result set can obtain data from different tables. To retrieve data from multiple tables, the SQL Server allows you to apply joins. Joins allow you to view data from related tables in a single result set. You can join more than one table based on common attribute.

Depending on the requirements to view data from multiple tables, you can apply different types of joins listed below:

Inner Join

An inner join retrieves records from multiple tables by using a comparison operator on a common column. When an inner join is applied, only rows with values satisfying the join condition in the common column are displayed. Rows in both tables that do not satisfy the join condition are not displayed.

A join is implemented by using the SELECT statement, where the SELECT list contains the name of the columns to be retrieved from the tables. The FROM clause contains the names of the tables from which combined data is to be retrieved. The WHERE clause specifies the condition, with a comparison operator, based on which the tables will be joined.

The syntax of applying an inner join in the SELECT query is:

SELECT column_name, column_name [, column_name]
FROM table1_name JOIN table2_name
ON table1_name.ref_column_name join_operator
Table2_name.ref_column_name

Where
  • Table1_name and table2_name are the name of the tables that are joined join_operator is the comparison operator based on which the join is applied
  • Table1_name.ref_column_name and table2_name.ref_column_name are the names of the columns on which the join is applied.
Whenever a column is mentioned in a join condition, the column should be referred by prefixing it with the table name to which it belongs or with a table alias. A table alias is a name defined in the FROM clause of the SELECT statement to refer to the table with another name or to uniquely identify the table.

The following query displays the Employee ID and Title for each employee from the Emoloyee table and the Rate and PayFrequency columns from the EmployeePayHistory table

SELECT e.BusinessEntityID,e.JobTitle, eph.Rate, eph.PayFrequency
from HumanResources.Employee e
JOIN HumanResources.EmployeePayHistory eph
ON e.BusinessEntityID = eph.BusinessEntityID

In the preceding query, the Employee and EmployeePayHistory tables are joined on the common column, ID. The query also assigns e as the alias of the Employee table and eph as the alias of the EmployeePayHistory table. The column names are also listed with the table alias names.

How to Querying Data using Joins in Sql Programming

Based on the relationship between tables, you need to select the common column to set the join condition. In the preceding example, the common column is ID, which is the primary key of the Employee table and the foreign key of the EmployeePayHistory table.

Observe the result set after the join. The record of the employee with D as 1 from the Employee table is joined with the record of the employee with ID as 1 from the EmployeePayHistory table.

© Copyright 2013 Computer Programming | All Right Reserved