Topic 6 Modifying Table Dat Flashcards
Joins allow the
retrieval of related
data spread across
multiple tables in a
relational database.
- Combine Data
from Multiple
Tables
Joins leverage primary
and foreign key
relationships to link
rows in one table to
corresponding rows in
another.
- Establish
Relationships Between
Tables
By retrieving only the
required data from
multiple tables, JOIN
operations reduce
redundancy and improve
query performance.
- Optimize Query
Efficiency
Joins enable complex
queries that
consolidate
information for
reporting and analytics.
- Support Advanced
Data Analysis
Different types of JOINs
(INNER, LEFT, RIGHT,
FULL OUTER, CROSS)
provide flexibility in
fetching data depending
on the analysis needs.
- Allow Versatile Data
Retrieval
By linking related
tables, JOINs help
maintain normalized
database structures
without duplicating
data.
- Enhance Data
Integrity
JOINs mimic real-world
connections between
entities, making it
easier to model and
query practical
relationships.
- Facilitate Real-World
Scenarios
is used to
combine rows from two or
more tables, based on a
related column between
them.
JOIN clause
SYNTAX OF
JOIN
SELECT Orders.OrderID,
Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Cus
tomers.CustomerID;
Returns records that
have matching values in both tables
INNER JOIN
Returns all records from
the left table, and the matched
records from the right table
LEFT JOIN:
Returns all records from
the right table, and the matched
records from the left table
RIGHT JOIN
Returns all records
from both tables
CROSS JOIN
SUPPORTED
TYPES OF
JOINS IN
MYSQL
Inner, left, right and cross join
does not require each record in the two joined tables to have a
matching record
OuterJoin
OUTER JOIN is subdivided into
Left Join
Right Join
Full Join
-returns all rows from the left table (or the first table), even if there are no
matches in the right table
Left Join
-returns all the rows from the right table (or the second table), even if there are
no matches in the left table
Right Join
- both tables are secondary (or optional), such that if rows are being matched in
table A and table B, then all rows from table A are displayed even if there is no matching
row in table B, and vice versa
Full Join
are SELECT statements that
have been saved in the
database
VIEWS
Keywords related to
Views:
- CREATE VIEW
- ALTER VIEW
- DROP VIEW
is a keyword that enables users to create new views
CREATE VIEW
syntax of creating views
CREATE VIEW ViewName AS SELECT statement
is the keyword used to
modify a created view
ALTER VIEW
Syntax of Altering view
ALTER VIEW ViewName AS
SELECT statement
is used to delete a view that was previously created
DROP VIEW
syntax of drop view
DROP VIEW ViewName
