Here you will find some of the basic definitions in SQL along with the commands and their syntax.

Join definition: A join is a way of searching for something across tables by using shared values to match up the tables.

JOIN
Return rows when there is at least one match in both tables.
The simplest form is:
SELECT * FROM table1,table2 WHERE table1.id=table2.id;
This yields the complete contents of whichever rows in the two tables share ID numbers. In MySQL, more specific and extensive types of joins exist, including LEFT or RIGHT joins, STRAIGHT or CROSS joins, INNER and OUTER joins, and SELF join.
 

Inner Join
The INNER JOIN keyword return rows when there is at least one match in both tables.
Example: If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.
 

LEFT JOIN
Return all rows from the left table, even if there are no matches in the right table.
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
Example: The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).
 

RIGHT JOIN
Return all rows from the right table, even if there are no matches in the left table.
Example: The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
 

FULL JOIN
Return rows when there is a match in one of the tables.
Example: The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.
 

Union
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
 

Wildcards

There are times when we want to match on a string pattern. To do that, we will need to employ the concept of wildcard. In SQL, there are two wildcards:

select xxxx from tablename where columnname like '%x%';

% (percent sign) represents zero, one, or more characters.
_ (underscore) represents exactly one character.

Wildcards are used with the LIKE keyword in SQL.

Below are some wildcard examples:
• 'A_Z': All string that starts with 'A', another character, and end with 'Z'. For example, 'ABZ' and 'A2Z' would both satisfy the condition, while 'AKKZ' would not (because there are two characters between A and Z instead of one).
• 'ABC%': All strings that start with 'ABC'. For example, 'ABCD' and 'ABCABC' would both satisfy the condition.
• '%XYZ': All strings that end with 'XYZ'. For example, 'WXYZ' and 'ZZXYZ' would both satisfy the condition.
• '%AN%': All strings that contain the pattern 'AN' anywhere. For example, 'LOS ANGELES' and 'SAN FRANCISCO' would both satisfy the condition.
• '_AN%': All strings that contain a character, then 'AN', followed by anything else. For example, 'SAN FRANCISCO' would satisfy the condition, while 'LOS ANGELES' would not satisfy the condition.
 

SQL Constraints

Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

Following are the constraints:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
 

SQL NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
 

Primary Key
A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table.

Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).
 

Foreign Key
A foreign key is a field (or fields) that points to the primary key of another table.
 

Composite Key
When multiple fields are used as a primary key, they are called a composite key.
 

SQL UNIQUE Constraint
The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.