Tutorial by Examples

Inner join returns only those records/rows that match/exists in both the tables based on one or more conditions (specified using ON keyword). It is the most common type of join. The general syntax for inner join is: SELECT * FROM table_1 INNER JOIN table_2 ON table_1.column_name = table_2.col...

A cross join is a Cartesian join, meaning a Cartesian product of both the tables. This join does not need any condition to join two tables. Each row in the left table will join to each row of the right table. Syntax for a cross join: SELECT * FROM table_1 CROSS JOIN table_2 Example: /* Sample...

Left Outer Join LEFT JOIN returns all rows from the left table, matched to rows from the right table where the ON clause conditions are met. Rows in which the ON clause is not met have NULL in all of the right table's columns. The syntax of a LEFT JOIN is: SELECT * FROM table_1 AS t1 LEFT JOIN ta...

Joins can also be used in an UPDATE statement: CREATE TABLE Users ( UserId int NOT NULL, AccountId int NOT NULL, RealName nvarchar(200) NOT NULL ) CREATE TABLE Preferences ( UserId int NOT NULL, SomeSetting bit NOT NULL ) Update the SomeSetting column of the Prefere...

Joining on a subquery is often used when you want to get aggregate data (such as Count, Avg, Max, or Min) from a child/details table and display that along with records from the parent/header table. For example, you may want to retrieve the top/first child row based on Date or Id or maybe you want a...

A table can be joined onto itself in what is known as a self join, combining records in the table with other records in the same table. Self joins are typically used in queries where a hierarchy in the table's columns is defined. Consider the sample data in a table called Employees: IDNameBoss_ID1...

Joins can also be used in a DELETE statement. Given a schema as follows: CREATE TABLE Users ( UserId int NOT NULL, AccountId int NOT NULL, RealName nvarchar(200) NOT NULL ) CREATE TABLE Preferences ( UserId int NOT NULL, SomeSetting bit NOT NULL ) We can delete rows...

Outer joins return all the rows from one or both tables, plus matching rows. Table People PersonID FirstName 1 Alice 2 Bob 3 Eve Table Scores PersonID Subject Score 1 Math 100 2 Math 54 2 Science 98 Left joining the tables: Select * f...