D426 Special opoerators and clauses / JOIN queries

BINARY

LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by BINARY keyword

aggregate function

processes values from set of rows and returns a summary value

COUNT (coutns number of rows in set)

MIN (finds minimum value in set)

MAX (finds maximum value in the set)

SUM (sum of all values)

AVG (computes arithmetic mean of all values in set)

GROUP BY

it computes aggregate function separately and returns one row for each group. the HAVING clause is used with this GROUP BY to filter group results

join clause

determines how a join query handles unmatched rows.

INNER JOIN

selects only matching left and right table rows

FULL JOIN

selects all left and right table rows regardless of match

ON

ON Clause specifies the join columns

LEFT JOIN

selects all left table but only matching right table rows

RIGHT join

select all right table rows but only matching left table rows

OUTER join

join that selects unmatched rows including left right and full joins

UNION

union combines the results of two SELECT clauses into one result table

CROSS JOIN

combines two tables without comparing columns

uses CROSS JOIN without ON clause