Inner joins are used to combine rows from two or more tables based on a related column between them. The result set contains only the rows where there is a match between the columns in the joined tables according to the join condition.
Explanation:
1. Joining Tables: In a database, data is often spread across multiple tables. For example, in a bookstore database, information about authors may be stored in one table, while details about books are stored in another.
2. Common Column: Inner joins are used to connect these tables based on a common column. This common column typically represents a relationship between the data in the tables. For example, in our bookstore scenario, the author ID in the authors table might correspond to the author ID in the books table.
3. Matching Rows: When you perform an inner join, the database compares the values in the common column of each row in the first table with the values in the common column of each row in the second table. If there is a match, the database includes the combined row in the result set. If there is no match, the row is excluded from the result set.
4. Result Set: The result set of an inner join contains only the rows where there is a match between the common columns of the joined tables. This means that the result set will only include rows where the data from both tables satisfy the join condition.
We are going to use “Pubs” database, there are several tables including “authors,” “titles,” “publishers,” and “titleauthor.” We’ll focus on explaining INNER JOINS using these tables.
Authors Table: Contains information about authors such as their ID, first name, and last name.
Titles Table: Stores details about books including the title, publication date, and publisher ID.
Publishers Table: Holds information about publishers such as their ID, name, and city.
TitleAuthor Table: Serves as a junction table linking authors with the books they have written.
INNER JOINS with examples:
Example 1: Retrieve authors and their books
SELECTÂ Titles.TitleID, Titles.Title, Authors.AuthorFirstName, Authors.AuthorLastName
FROM Titles
INNER JOIN TitleAuthor ONTitles.TitleID = TitleAuthor.TitleID
INNER JOIN Authors ONTitleAuthor.AuthorID = Authors.AuthorID
Result:
Example 2: Display publishers and their books’ titles
SELECT publishers.PublisherName, titles.title
FROM publishers
INNER JOIN titles ON publishers.PublisherID = titles.PublisherID
Example 3: Show authors and the cities of their publishers
SELECT authors.AuthorID, authors.AuthorFirstName, authors.AuthorLastName, publishers.PublisherCity
FROM authors
INNER JOIN titleauthor ON authors.AuthorID = titleauthor.AuthorID
INNER JOIN titles ON titleauthor.TitleID = titles.TitleID
INNER JOIN publishers ON titles.PublisherID = publishers.PublisherID;
Result:
Subqueries:
A subquery, known as an inner query or nested query, which is a query nested within another query. It allows you to use the result of one query as a condition or value in another query. Subqueries are a powerful feature of SQL that enable you to perform more complex and dynamic data manipulation and retrieval operations.
Purpose of Subqueries:
1. Filtering Data: Subqueries can be used to filter rows based on specific conditions. For example, you can use a subquery to select rows from one table based on the values returned by another query.
2. Calculating Values: Subqueries can calculate values dynamically based on the result of another query. This is useful for performing calculations or aggregations on subsets of data.
3. Comparing Data: Subqueries allow you to compare data from different tables or columns within the same table. You can use subqueries in conditions such as IN, EXISTS, or comparisons (=, >, <, etc.).
4. Using Aggregates: Subqueries can include aggregate functions (e.g., SUM, AVG, COUNT) to calculate summary values from subsets of data.
Types of Subqueries:
1. Scalar Subquery: Returns a single value and can be used wherever an expression is allowed, such as in the SELECT list, WHERE clause, or HAVING clause.
2. Row Subquery: Returns a single row of data and can be used wherever a row value is allowed, such as in a comparison operation or a FROM clause.
3. Table Subquery: Returns a result set of multiple rows and columns and can be used as a table in the FROM clause of a SELECT statement.
Example of Subquery:
Suppose we have two tables: “employees” and “departments.” We want to retrieve the names of employees who work in the ‘Sales‘ department. We can use a subquery to achieve this:
SELECT employee_name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = ‘Sales’
);