JOINs Flashcards
Data Query Language (DQL):
primary command is SELECT, which is used to fetch data from a database.
Data Manipulation Language (DML)
inserting, updating, deleting, and managing data within database objects. The core commands are INSERT,
UPDATE, and DELETE.
Data Definition Language (DDL):
defining and modifying the database structure or schema. Includes commands like CREATE, ALTER, and DROP which can be used to create, alter, and delete databases and their objects (like tables, indexes, constraints, etc.).
Data Control Language (DCL):
controlling access to the data in the database. Commands such as GRANT and REVOKE are used to manage
database security and user permissions.
SELECT statement
retrieve data from a database. I
SELECT statement can be used with other clauses like WHERE, GROUP BY,
HAVING, ORDER BY to filter, sort, group or make calculations with data (
SELECT
Returns the final data (as functionality not display)
FROM …
JOIN
Chose and JOIN tables to get base data
GROUP BY
Aggregates the base data
HAVING
Filters the aggregated data
JOIN
operation that combines rows from two or more tables based on a related column between them (The PKs/FKs).
INNER JOIN:
a simple JOIN, it returns rows when there is a match in both tables.
LEFT JOIN:
Returns all rows from the LEFT TABLE and the matched rows from the RIGHT
TABLE. If no match is found, the result is NULL on the right side.
Right Join
Returns all rows from the RIGHT TABLE and the matched rows from the LEFT
TABLE. If no match is found, the result is NULL on the left side.
FULL JOIN
Returns rows when there is a match in one of the tables. If there is no match, the result is NULL on either side.
SELF JOIN:
self join is a regular join, but the table is joined with itself.
