1/23
Looks like no tags are added yet.
IN
An operator that is used in the WHERE clause to check if a specified column's value matches any value within a provided list; marks ‘True’ if it does.
Format:
SELECT column_name(s)
FROM table_name
WHERE column_name (TERM) (value1, value2, ...);
Example:
SELECT * FROM Customers
WHERE Country (TERM) ('Germany', 'France', 'UK');
NOT IN
An operator that is used in the WHERE clause to determine if a specified column’s value does not match any value within a provided list, marks ‘True’ if it doesn’t.
Format:
SELECT column_name(s)
FROM table_name
WHERE column_name (TERM) (value1, value2, ...);
Subquery / Subselect
A SELECT query contained within another SQL statement, commonly another SELECT, which helps create dynamic functions;
Result: The inner SELECT statement is inserted (with parentheses) into the outer statement.
Example 01:
SELECT * FROM table_A
WHERE col_A=(SELECT max(col_A) from table_A);
(Looks to see if each column value matches the maximum column value from the table)
Example 02:
SELECT * FROM Students
WHERE Score >=(SELECT AVG(Score) from Students)
(Looks for scores that are greater than or equal to the average)
JOIN
An operator that is used to combine rows from two or more tables, based on a related column between them.
Cartesian Product
The combination of every row in one table with every row of a second table.
The resulting table has all columns of both tables (even duplicates)
If the input table of a Cartesian Product has {A} and {B} number of rows, the output table has {A x B} number of rows
Sets of all ordered pairs (a, b) where a is an element of A and b is an element of B.

SELECT <select_list>
From Table A A
INNER JOIN in Table B B
ON A.Key = B.Key
Write what this signifies in MySQL.

SELECT <select_list>
From Table A A
LEFT JOIN in Table B B
ON A.Key = B.Key
Write what this signifies in MySQL.

Scalar Subquery
A subquery that returns a single value, which is often used in the SELECT list and WHERE clause (like finding the average employee salary)
The subquery is used in the WHERE clause test condition!
They both involve the same table!

Column Subquery
A subquery that returns a single column of values, which can be sued where a column is expected in the outer query; l typically used in combination with the IN or NOT IN operators

Row Subquery
A subquery that lets you return a row with multiple columns, there are less common but useful when you need to compare multiple values from a subquery

Table Subquery
A subquery that returns an entire table, which can be used in the outer query like a regular table (often used in the FROM clause and can be handy for complex filtering and transformations that are hard to express otherwise)
(Example: Creates a temporary table of sales from last month, outer query calculates the total quantity sold fore ach product)

Correlated Subquery
A subquery that contains a reference to a table or column that is outside of the scope of the subquery:
Details about the example:
The outer query goes through the employee table EMP, and gives that table the nickname X
The inner query looks at the project table (PROJ), in which X.WORKDEPT refers to the Employee row to get the work department for each employee —> “Give me every designer whose department has at least one project under major project MA2100.”

Noncorrelated Subquery
A subquery in which the inner and outer query are independent of each other, the inner query is evaluated once and the result is passed to the outer query as it if it was a literal (or static) value.
In fact, the inner query may not even be aware that the outer query exists!

No!
Are subqueries limited to only having one inner subquery for an outer subquery?
The attached example: Selects first and last names of authors if their income percentage is greater than the average collective income percentage!

The outer subquery typically provides the returned output, while the inner subquery usually filters the data!
What are the typical roles and responsibilities of the outer query versus the inner query?
INNER JOIN
Returns only rows that have matching values in both tables; typically the default JOIN type.
Identical to a CROSS JOIN, but is intended to be used with a join condition. Functionally similar to a WHERE clause, the join condition is used to limit the rows returned from the join input
Difference from WHERE: A join condition intends to return rows whose columns are related in a meaningful manner

Recombines them back together in order to retrieve information
What is the primary mechanism of JOIN, serving its purpose since normalization predominantly breaks data into separate tables?
ON (ON Clause)
A fundamental component of SQL Joins, allowing you to specify the conditions for joining tables.
INNER JOIN
What is the following an example of?

NATURAL JOIN
A JOIN that automatically matches and combines rows from two tables based on columns with the same name and compatible data types!
Unlike other joins, you don't need to specify a condition manually - SQL detects common columns and returns a result without redundant duplicate columns!
Equi-Joins
Join conditions that are based on the equality operator (Natural Join, Inner Join)

Non-Equi Joins
Less common join conditions that use other comparison operators in the join condition.
INCLUDES:
Other comparison operators such as >, >=, <, <=, <>. BETWEEN, and LIKE

OUTER JOIN
A JOIN clause that keeps the matching rows and also keeps rows that have no match, in which the missing values are filled with NULL.
LEFT (term): Keeps every row from the left table
RIGHT (term): Keeps every row from the right table
FULL (term): Keeps every row from both tables