An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate.

The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Consider the following two tables,

Employee table

Department table

The queries given in the examples above will join the Employee and Department tables using the DepartmentID column of both tables. Where the DepartmentID of these tables match (i.e. the join-predicate is satisfied), the query will combine the LastName, DepartmentID and DepartmentName columns from the two tables into a result row. Where the DepartmentID does not match, no result row is generated.

Thus the result of the execution of either of the two queries above will be:

The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Consider the following two tables,

Employee table

LastName | DepartmentID |
---|---|

Robert | 31 |

Jones | 33 |

Steinberg | 33 |

Robinson | 34 |

Smith | 34 |

John | NULL |

Department table

DepartmentID | DepartmentName |
---|---|

31 | Sales |

33 | Engineering |

34 | Clerical |

35 | Marketing |

SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID;

The queries given in the examples above will join the Employee and Department tables using the DepartmentID column of both tables. Where the DepartmentID of these tables match (i.e. the join-predicate is satisfied), the query will combine the LastName, DepartmentID and DepartmentName columns from the two tables into a result row. Where the DepartmentID does not match, no result row is generated.

Thus the result of the execution of either of the two queries above will be:

Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|

Robert | 34 | Clerical | 34 |

Jones | 33 | Engineering | 33 |

Smith | 34 | Clerical | 34 |

Steinberg | 33 | Engineering | 33 |

Rafferty | 31 | Sales | 31 |