Basics of SQL Part 6
SQL Full Outer Join
The 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 table1.col1, table2.col2...
from table1
left join table2
on table1.commonfield=table2.commonfield;
union
select table1.col1, table.col2....
from table1
right join table2
on table1.commonfield=table.2.commonfield;
Sample: select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name
from emp e
left join dept d
on e.dept_id=d.dept_id
union
select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name
from emp e
right join dept d
on e.dept_id=d.dept_id;
SQL Cross Join
- Assume there are 4 records in table 1 & 3 records in table 2
Syntax: select * from table1.cross join table2;
Subquery
A subquery is a query within another SQL query that is embedded within the where clause, from clause, & having clause.
Syntax: select column_name from table name where column_name expression operator ( select column_name from table name);
Sample: select emp_name, dept, salary from emp where salary > (select avg(salary) from emp);
Stored Procedure
- A stored Procedure is an SQL code that we can save so that code can be reused over and over again.
Syntax: delimiter &&
create procedure procedure name
begin
select column name from table name where condition
end &&
delimiter;
Sample: delimiter &&
create procedure top_players()
begin
select name, country, goals from players where goal >6;
end &&
delimiter;
To run a stored procedure query
Syntax: call procedure_name();
Sample: call top_players();
#sql #storedprocedure #day6
Mail id: arpitmishra1629.ftp@gmail.com
Contact no: 8127430702
Linkedin: https://www.linkedin.com/in/arpit-mishra-9319b5131
👍🇮🇳
ReplyDelete