#Day5 #100DaysOfCode
_join is used to connect table of matching rows or columns.
_inner join or just join is used to show results in BOTH tables
_left join or left outer join is used to show results in BOTH tables,
but also results that right table does not have
_right join or right outer join is used to show results in BOTH tables,
but also results that left table does not have
_full join: combination of left and right join.
_implicit join is using join without specifying the join keyword,
instead use WHERE
_explicit join is using join by specifying the join keyword.
https://timoday.edu.vn/bai-3-cau-lenh-truy-van-du-lieu/
https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/
_IN operator uses for select columns. it uses instead of join
select SNo
from Supplier
where SNo in (select SNo in Shipment
where PNo like 'P1')
_EXISTS: the result of query on one table exists in another table.
It must have an implicit join statement.
select SNo
from Supplier
where exists (select*from Shipment
where Supplier.SNo = Shipment.SNo
and PNo like 'P1')
_NOT IN, NOT EXISTS are opposite of IN, EXISTS
Example: show the suppliers who have never made a shipment
select SNo, SName, Status, City
from Supplier
where SNo not in (select SNo from Shipment)
select SNo, SName, Status, City
from Supplier
where not exists (select*from Shipment
where Shipment.SNo = Supplier.SNo)
_ORDER BY: by default is ASC, but there is DESC
_GROUP BY: whenever there is anything on GROUP BY,
there should be that thing on SELECT
except for COUNT, SUM, AVERAGE, MIN, MAX
_can use naming for columns. i.e: sum(Qty) as TotalShip
_can use alias for table name: i.e:
select pa.PNo, su.City
from Part pa, Supplier su
_UNION returns UNIQUE rows from each query
(its like concatenation but only shows the rows one time)
_UNION ALL returns all rows from both queries,
including the duplicates.
_INTERSECT returns the common UNIQUE rows from each query.
_EXCEPT returns unique rows from the first query
that aren’t present in the second query.
https://dotnettutorials.net/lesson/differences-between-union-except-and-intersect-operators-in-sql-server/
_join is used to connect table of matching rows or columns.
_inner join or just join is used to show results in BOTH tables
_left join or left outer join is used to show results in BOTH tables,
but also results that right table does not have
_right join or right outer join is used to show results in BOTH tables,
but also results that left table does not have
_full join: combination of left and right join.
_implicit join is using join without specifying the join keyword,
instead use WHERE
_explicit join is using join by specifying the join keyword.
https://timoday.edu.vn/bai-3-cau-lenh-truy-van-du-lieu/
https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/
_IN operator uses for select columns. it uses instead of join
select SNo
from Supplier
where SNo in (select SNo in Shipment
where PNo like 'P1')
_EXISTS: the result of query on one table exists in another table.
It must have an implicit join statement.
select SNo
from Supplier
where exists (select*from Shipment
where Supplier.SNo = Shipment.SNo
and PNo like 'P1')
_NOT IN, NOT EXISTS are opposite of IN, EXISTS
Example: show the suppliers who have never made a shipment
select SNo, SName, Status, City
from Supplier
where SNo not in (select SNo from Shipment)
select SNo, SName, Status, City
from Supplier
where not exists (select*from Shipment
where Shipment.SNo = Supplier.SNo)
_ORDER BY: by default is ASC, but there is DESC
_GROUP BY: whenever there is anything on GROUP BY,
there should be that thing on SELECT
except for COUNT, SUM, AVERAGE, MIN, MAX
_can use naming for columns. i.e: sum(Qty) as TotalShip
_can use alias for table name: i.e:
select pa.PNo, su.City
from Part pa, Supplier su
_UNION returns UNIQUE rows from each query
(its like concatenation but only shows the rows one time)
_UNION ALL returns all rows from both queries,
including the duplicates.
_INTERSECT returns the common UNIQUE rows from each query.
_EXCEPT returns unique rows from the first query
that aren’t present in the second query.
https://dotnettutorials.net/lesson/differences-between-union-except-and-intersect-operators-in-sql-server/