- A join is used to combine columns from two or more tables into a single result set.
- To join data from two tables you write the names of two tables in the FROM clause along with JOIN keyword and an ON phrase that specifies the join condition.
- The join condition indicates how two tables should be compared.
- In most cases they are compares on the base on the relationship of primary key of the first table and foreign key of the second table.
We have three types of joins
- Inner Join
- Outer Join
- Cross Join
1. Inner Join: Inner Join is the default type of join, it will produces the result set, which contains matched rows only.
An Inner Join will take two tables and join them together based on the values in common columns (linking field) from each table. Syntax: select * from table1<innerjoin>table2 Create table employee(EmployeeID int,EmployeeName varchar(20),DepartmentID int) Employee TableEmployeeID | EmployeeName | DepartmentID |
1 | Smith | 1 |
2 | Jack | 2 |
3 | Jones | 3 |
4 | Andrews | 3 |
5 | Dave | 5 |
6 | Jospeh | NULL |
Create table deparment(DepartmentID int,DepartmentName varchar(20)) Department TableDepartmentID | DepartmentName |
1 | HR |
2 | Finance |
3 | Security |
4 | Sports |
5 | HouseKeeping |
6 | Electrical |
Example 1:- To retrieve only the information about those employees who are assigned to a department
Syntax: Select employee.EmployeeID,employee.EmployeeName,deparment.DepartmentName
From employee Inner Join deparment on employee.DepartmentID = deparment.DepartmentID The Result Set will be:-
1 Smith HR 2 Jack Finance 3 Jones Security 4 Andrews Security 5 Dave HouseKeeping Example 2:- Retrieve only the information about departments to which at least one employee is assigned.
Syntax: Select deparment.DepartmentID,deparment.DepartmentName
From deparment Inner Join employee on employee.DepartmentID = deparment.DepartmentID The ResultSet will be:-
1 HR 2 Finance 3 Security 3 Security 5 HouseKeeping 2. Outer Join: Outer join produces the results, which contains matched rows and unmatched rows. Here we have three types of joins,
1. Left Outer Join
2. Right Outer Join 3. Full Outer Join Left Outer Join: Left Outer Join producess the results, which contains all the rows from Left table and matched rows from Right Table.
Syntax: select * from table1<leftouterjoin>table2
Example 1:- To retrieve the information of all the employees along with their Department Name if they are assigned to any department.
Syntax: Select employee.EmployeeID,employee.EmployeeName,deparment.DepartmentName
From employee LEFT OUTER JOIN deparment on employee.DepartmentID = deparment.DepartmentIDThe ResultSet will be:-
1 Smith HR 2 Jack Finance 3 Jones Security 4 Andrews Security 5 Dave HouseKeeping 6 Joseph NULL Right Outer Join: Right Outer Join procuresses the result set, which contains all the rows from right table and matched rows from left table.
Syntax: Select * from table1<right outer join>table2
Example 2:- use Right Outer join to retrieve the information of all the departments along with the detail of EmployeeName belonging to each Department, if any is available
Syntax: Select deparment.DepartmentID,deparment.DepartmentName,employee.EmployeeName From employee RIGHT Outer Join deparment on employee.DepartmentID = deparment.DepartmentID
The ResultSet will be:-
1 HR Smith 2 Finance Jack 3 Security Jones 3 Security Andrews 4 Sports NULL 5 HouseKeeping Dave 6 Electricals NULL This query will result in Null value for Employee Name where no Employee is assigned to that department Full Outer Join: Full Outer Join procuresses the result set, which contains all the rows from left table and all the rows from right table.
Ø A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
Syntax: select * from table1<fullouterjoin>table2
Syntax: Select * From employee Full Outer Join deparment on employee.DepartmentID = deparment.DepartmentID
The ResultSet will be :-1 Smith 1 1 HR
2 Jack 2 2 Finance 3 Jones 3 3 Security 4 Andrews 3 3 Security 5 Dave 5 5 HouseKeeping 6 Joseph NULL NULL NULL NULL NULL NULL 4 Sports NULL NULL NULL 6 Electricals 3. Cross Join: A join without having any condition is known as Cross Join, in cross join every row in first table is joins with every row in second table.
Syntax: select * from table1<cross join>table2
select * from employee cross join deparment
The ResultSet will be:-
1 smith 1 1 HR 2 jack 2 1 HR 3 jones 3 1 HR 4 Andrews 3 1 HR 5 dave 5 1 HR 6 Joseph NULL 1 HR 1 smith 1 2 Finance 2 jack 2 2 Finance 3 jones 3 2 Finance 4 Andrews 3 2 Finance 5 dave 5 2 Finance 6 Joseph NULL 2 Finance 1 smith 1 3 Security 2 jack 2 3 Security 3 jones 3 3 Security 4 Andrews 3 3 Security 5 dave 5 3 Security 6 Joseph NULL 3 Security 1 smith 1 4 Sports 2 jack 2 4 Sports 3 jones 3 4 Sports 4 Andrews 3 4 Sports 5 dave 5 4 Sports 6 Joseph NULL 4 Sports 1 smith 1 5 HouseKeeping 2 jack 2 5 HouseKeeping 3 jones 3 5 HouseKeeping 4 Andrews 3 5 HouseKeeping 5 dave 5 5 HouseKeeping 6 Joseph NULL 5 HouseKeeping 1 smith 1 6 Electricals 2 jack 2 6 Electricals 3 jones 3 6 Electricals 4 Andrews 3 6 Electricals 5 dave 5 6 Electricals 6 Joseph NULL 6 Electricals User Defined Functions There are three types of user-defined functions that we can create 1. Scalar functions
2. Inline-table valued UDF
3. Multi-statement table valued UDF
1. Scalar functions Ø Scalar functions evaluate scalar values and return the output like a normal c program.
Ø Which can accept up to 1,024 input parameters and return one scalar (simple data type) value.
Syntax: CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [ AS ] data_type }[ ,...n ] ] ) RETURNS data_type [ AS ] BEGIN function_body RETURN scalar_expression END
Ø "CREATE FUNCTION" statement to create our own UDF. On the first line, we must specify the name that we want our UDF to be known as.
Ø We can also optionally specify the owner of the UDF, like this: CREATE FUNCTION dbo.myFunction
Ø Next, we have the optional parameter list.
Ø Here's a parameter list that accepts two integer values and one variable character value (just like stored procedures, we can reference these input parameters in the body of our UDF):
Ø @int1 INT, @int2 INT, @vc1 VARCHAR(50)
Ø
Ø The RETURNS statement tells SQL Server the type of scalar variable that our function will be returning.
Ø RETURNS INT
Ø Next is the optional AS keyword, which is used to explicitly tell SQL Server where the function body starts
Ø The actual body of our UDF is surrounded with BEGIN and END keywords
BEGIN
-- Body of our UDF goes here
-- RETURN statement and value go here
END
-- Body of our UDF goes here
-- RETURN statement and value go here
END
Example for scaler function
CREATE FUNCTION dbo.mult
(
@num1 INT,
@num2 INT
)
RETURNS INT
AS
BEGIN
RETURN (@num1 * @num2)
END
1. Our new function, named "mult", accepts two integer values and returns the product of those two values.
(
@num1 INT,
@num2 INT
)
RETURNS INT
AS
BEGIN
RETURN (@num1 * @num2)
END
1. Our new function, named "mult", accepts two integer values and returns the product of those two values.
2. To test our UDF, delete all of the code in the query window and enter the following code:
Testing the SCALER function
DECLARE @result INT
SET @result = dbo.mult(5, 10)
PRINT @result
SET @result = dbo.mult(5, 10)
PRINT @result
Ø Output into the results window should look like this:
Ø You'll notice that I've called our scalar UDF using a two-part name in the form of ownerName.functionName.
Ø I could've also used a three-part name in the form of databaseName.ownerName.functionName such as "PUBS.dbo.mult".
Ø Using a two/three part name to call our UDF is mandatory and helps SQL Server to distinguish between our UDF's and system level functions.
Creating an inline-table valued UDF
An inline-table valued UDF allows us to use a single select query in the body of our UDF to return a set of rows from any specific table.
Syntax:
CREATE FUNCTION [ owner_name. ] function_name
(
[ { @parameter_name [ AS ] data_type } [ ,...n ] ]
)
RETURNS TABLE
[ AS ]
RETURN [ ( ] select-stmt [ ) ]
(
[ { @parameter_name [ AS ] data_type } [ ,...n ] ]
)
RETURNS TABLE
[ AS ]
RETURN [ ( ] select-stmt [ ) ]
- As you can see, the signature of the "CREATE FUNCTION" for an inline-table valued UDF is exactly the same as that for a scalar UDF except that it returns a "TABLE" variable type and has no body.
- Instead of containing a function body, inline-table valued UDF's specify a select statement after the RETURN keyword.
- This select statement is executed and all of its matching rows are returned as the value of the function.
Example for inline-table valued UDF
Create a authors table:
create table authors(au_id int,au_lname varchar(20),au_fname varchar(20),state varchar(20))
Inserting data into table:
insert into authors values(10,'kiran','kumar','Andhrapradesh')
insert into authors values(10,'Rajesh','Yadhav','Andhrapradesh')
insert into authors values(30,'praveen','sadhu','Karnataka')
insert into authors values(40,'varma','Raghu','Tamilnadu')
Example
CREATE FUNCTION getAuthorsByState
(
@state varchar(20)
)
RETURNS TABLE
AS
RETURN
(
SELECT au_fname + ' ' + au_lname AS aName
FROM authors
WHERE state = @state
)
- We've just created a new UDF named "getAuthorsByState", which will return a list of names of authors whose state field matches the value passed in through the input parameter, @state.
- As you can see, we have used a basic SQL query to concatenate the values of the au_fname and au_lname fields with a space.
- In the where clause, we tell SQL Server to only return authors whose state matches the value of our @state input parameter.
- To test our new inline-table valued UDF, clear the code window in query analyzer and enter and execute the following code:
Testing the new inline_table valued UDF
SELECT * FROM getAuthorsByState('Andhrapradesh')
Creating a multi-statement table valued UDF
* Multi-statement UDF's are extremely powerful, and just like inline table valued UDF's, they allow us to use a select statement to return a result set.
Syntax:
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] data_type } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ AS ]
BEGIN
function_body
RETURN
END
( [ { @parameter_name [AS] data_type } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ AS ]
BEGIN
function_body
RETURN
END
Ø The first two lines of the function are no different to the other two types of UDF's that we've discussed, so we'll skip them.
Ø On the "RETURNS ..." line, we specify a variable of type TABLE that will be returned from the function, as well as the definition of each of the fields that will make up the table, like this:
Ø RETURNS @myTable TABLE
(
userId INT,
name VARCHAR(50),
state VARCHAR(20)
)
(
userId INT,
name VARCHAR(50),
state VARCHAR(20)
)
Ø In the actual body of our UDF, we use an insert statement to add rows to our table.
Ø We can use SQL's select statement to retrieve field values from any table in our database, just as long as that field's type matches the type specified in the table's field definitions:
Let's use the "jobs" table of the "pubs" database to create a simple multi-statement table valued UDF.
We will return a list of all jobs whose minimum level is greater than a certain number (which will be the input parameter to the function).
Creating jobs table:
create table jobs(job_id int,job_desc varchar(20),min_lvl int,max_lvl int)
insert into jobs1 values(10,'CEO',50,60)
insert into jobs1 values(20,'CTO',40,50)
insert into jobs1 values(30,'PM',30,40)
insert into jobs1 values(40,'PL',20,30)
Example for multi statement table value function
CREATE FUNCTION getJobsByMinLvl1
(
@minLvl int
)
RETURNS @jobTable TABLE
(
jobName VARCHAR(50),
difMinMax int
)
AS
BEGIN
INSERT @jobTable
SELECT job_desc,
(max_lvl - min_lvl)
FROM jobs
WHERE min_lvl >= @minLvl
RETURN
END
See how I've explicitly defined the fields that our table variable will return: jobName, which is a variable character field, and difMinMax, which is a int field.
Between the BEGIN and END statements, we use a select statement to get every job from the jobs table whose min_lvl field is greater than or equal to the input parameter, @minLvl.
Note that within this select statement, we could also grab field values from other tables to include in the resultant table, @jobTable.
Purpose for UDF
You must be wondering when we have Stored procedure why do we go for user-defined functions.
Well the simple reason is we can’t call stored procedure as part of an expression.
Testing the SCALER function
Example:
DECLARE @result INT
SET @result = dbo.mult(5, 10)
PRINT @result
SET @result = dbo.mult(5, 10)
PRINT @result
And we can’t even call the stored procedure in SELECT clause.
Example:
Testing the new inline_table valued UDF
SELECT * FROM getAuthorsByState('Andhrapradesh')
No comments:
Post a Comment