You are here
Home > Uncategorized > cross apply in sql

cross apply in sql

Cross Apply

  • Cross apply operates on left and right inputs.
  • It applies the right table expression to each row in left table.
  • It only returns match rows.
  • If right table expression returns empty row for left row and the left row is not return then operator behaves like a cross join between the right result set and that row.
  • Syntax:
  • Select column1, column2, …, columnN

    from tablename as alias_name1

    CROSS APPLY (table_expression) as alias_name2;

  • Examples:
  •  

    Table: Departments

     

    Id

    DepartmentName
    1

    IT

    2

    Admin
    3

    IT

    4

    HR

    5

    Admin
    6

    Sales

     

     

    Table: Employees

     

    E_Id

    EmployeeName Salary
    1 Anil

    88000

    2

    Smita 66000
    3 Anika

    44000

    4

    Shiv 22000
    5 Tina

    11000

    6

    Rina

    8000

     

     

  • Example-1:
  •  

    create function fn_getdata(@id int)

    returns table

    as

  • Return

    (

    Select * from dbo.Employees where E_Id=@id

  • )

     

    Select d.DepartmentName,e.EmployeeName,e.Salary

  • from dbo.Departments as d

    CROSS APPLY fn_getdata (d.id) as e;

     

  • Output:
  •  

    DepartmentName

    EmployeeName Salary
    IT Anil

    88000

    Admin

    Smita 66000
    IT Anika

    44000

    HR

    Shiv 22000
    Admin Tina

    11000

    Sales

    Rina

    8000

     

     

     

     

  • Example-2:
  •  

    select d.DepartmentName,e.E_Id,e.EmployeeName,e.Salary

  • from dbo.Departments as d

    cross apply

    (select E_Id,EmployeeName,Salary from dbo.Employees) as e;

     

     

  • Output:
  •  

    DepartmentName

    E_Id EmployeeName Salary
    IT 1 Anil

    88000

    Admin

    1 Anil 88000
    IT 1 Anil

    88000

    HR

    1 Anil 88000
    Admin 1 Anil

    88000

    Sales

    1 Anil 88000
    IT 2 Smita

    66000

    Admin

    2 Smita 66000
    IT 2 Smita

    66000

    HR

    2 Smita 66000
    Admin 2 Smita

    66000

    Sales

    2 Smita 66000
    IT 3 Anika

    44000

    Admin

    3 Anika 44000
    IT 3 Anika

    44000

    HR

    3 Anika 44000
    Admin 3 Anika

    44000

    Sales

    3 Anika 44000
    IT 4 Shiv

    22000

    Admin

    4 Shiv 22000
    IT 4 Shiv

    22000

    HR

    4 Shiv 22000
    Admin 4 Shiv

    22000

    Sales

    4 Shiv 22000
    IT 5 Tina

    11000

    Admin

    5 Tina 11000
    IT 5 Tina

    11000

    HR

    5 Tina 11000
    Admin 5 Tina

    11000

    Sales

    5 Tina 11000
    IT 6 Rina

    8000

    Admin

    6 Rina 8000
    IT 6 Rina

    8000

    HR

    6 Rina 8000
    Admin 6 Rina

    8000

    Sales

    6 Rina

    8000

     

     

     

    Keep visiting my site ask2tech.com for upcoming top technical article

    Learn more about DBA at TechNet Consultancy

Leave a Reply

Top