MySQL Fundamentals

Here are some notes on the Pluralsight course 'MySQL Fundamentals'.

Data Retrieval Techniques

Basic syntax of the SELECT Statement:

• SELECT Column_List
  • DISTINCT to retrieve unique records
• FROM Table_Name
• WHERE Filter_Condition
  • AND 
  • OR 
  • NOT
• ORDER BY Column_List
  • DESC
  • ASC (default if not specified)
• LIMIT Row_Limit
  • Limit
  • Offset, Limit

If don’t want to specify the database before each table, then use the USE command. E.g.:

SELECT * FROM dbo.users;

USE dbo;

SELECT * FROM users;

Arithmetic expressions order of precedence:

• Multiplication *
• Division /
• Integer Division DIV
• Modulo % or MOD
• Addition +
• Subtraction –

LIMIT Clause. Always recommended to use ORDER BY when use LIMIT as there’s no guarantee we’ll get the same data in every subsequent execution when we use LIMIT alone.

Joins, Unions and Subqueries

Joins

JOIN combines columns from 2 or more tables in a single result set.

• INNER JOIN
  • Returns rows when there is at least one match in both the tables

SELECT t1.*, t2.*

FROM Table1 t1

INNER JOIN Table2 t2 ON t1.ID = t2.ID;

• OUTER JOIN
  • Left Outer Join
    • Returns all the rows from the left table with the matching rows from the right table.
    • If no matching columns in the right table, NULL values are returned.
  • Right Outer Join
    • Returns all the rows from the right table with the matching rows from the left table.
    • If no matching columns in the left table, NULL values are returned.
  • Full Outer Join* - not supported by MySQL
    • Combines left outer join and right outer join.
    • Returns rows from either table when the conditions are met and returns a null value when there is no match.
• CROSS JOIN
  • Cartesian join that does not necessitate any condition to join.
  • The results set contains records that are multiples of the record number of both the tables.
• SELF JOIN
  • A table is joined with itself.
  • The user must alias tables used in a self join.
• EQUI JOIN
  • Specific type of comparator-based join that uses only equality comparison.
• NON-EQUI JOIN
  • Specific type of comparator-based join, that does not use equality comparisons in the join-predicate.
• NATURAL JOIN
  • Joins 2 or more tables based on all the columns in the two tables with the same name.
  • Can be either INNER or OUTER join.
• STRAIGHT JOIN
  • Discussed in Performance Tuning for MySQL Course.

Unions

• UNIONS combines 2 or more SELECT statements into a single result set.
• Each SELECT statement of UNION operator must have the same number of columns.
• UNION removes duplicate rows.
• UNION ALL does not remove duplicate rows.
• Only one ORDER BY clause sorting entire result set.
• Simulate FULL OUTER JOIN using LEFT and RIGHT join with UNION.

Sub-queries 

• A sub-query is a nested query where the results of one query can be used in another query via a relational operator or aggregation function.
• Must be enclosed with parentheses.
• A sub-query can have only one column in the SELECT clause if used in WHERE clause.
• An ORDER BY clause is not allowed in a subquery.
• Can be used in WHERE, HAVING, FROM and SELECT clause.