Basics of SQL Part 5
SQL Union
- Used to combine the result set of two or more select statements removing duplicates.
- Each select statement with the union must have the same number of columns.
- The selected columns must be of similar data types and must be in the same order in each select statement.
- More than 2 queries can be clubbed.
Syntax: select column name from table 1
union
select column name from table 2;
Sample: select product _name from items 1
union
select product _name from items 2;
SQL Union All
- Used to combine the results of two select statements including duplicate rows.
- The same rules that apply to the union clause will apply to the union all operator.
Syntax: select column name from table 1
union all
select column name from table 2;
Sample: select product _name from items 1
union all
select product _name from items 2;
SQL Joins
Combine rows/columns from two or more tables, based on a related column between them in a database.
SQL Inner Join:
The inner join creates a new result table by combining column values of two tables based upon the join predicate.
The query compares each row of table 1 with each row of table 2 to find all pairs of rows which satisfy the join predicate.
Syntax: select table1.col1, table2.col2, .....
from table1
inner join table2
on table1.commonfiled=table2.commonfiled;
Sample: select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name
from emp e
inner join dept d
on e.dept_id=d.dept_id;
SQL Left Join:
The left join returns all the values from the left table, plus matched values from the right table or null in case of no matching join predicate.
Syntax: select table1.col1, table2.col2
from table1
left join table2
on table1.commonfiled=table2.commonfiled;
Sample: select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name
from emp e
left outer join dept d
on e.dept.id=d.dept_id;
SQL Right Join
The right joins returns all the values from the right table, plus matched values from the left table or null in case of no matching join predicate.
Syntax: select table1.col1, table2.col2
from table1
right join table2
on table1.commonfiled=table2.commonfiled;
Sample: 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;
#data #sql #database #day5
Mail id: arpitmishra1629.ftp@gmail.com
Contact no: 8127430702
Linkedin: https://www.linkedin.com/in/arpit-mishra-9319b5131
Comments
Post a Comment