How to Join First Row in SQL?

Last Updated : 05 Dec, 2024

SQL (Structured Query Language) is a powerful tool used to manage and manipulate data in relational databases. A common requirement when dealing with data relationships is to fetch only the first row from a table based on specific conditions or criteria. This can be particularly useful when joining multiple tables, and we need to retrieve additional information from one table while only selecting its earliest or topmost row.

In this article, we will explain different methods for joining the first row in SQL, including syntax, step-by-step examples, and outputs to help us better understand these techniques.

What Is Joining the First Row in SQL?

When performing a JOIN operation in SQL, sometimes we only want to retrieve the first row from the related table based on a particular sorting order, such as the earliest order date, highest salary, or smallest ID. This ensures that our result set is concise and contains only the relevant data.

Examples of Joining the First Row in SQL

In this section, we will explore different methods to join tables and retrieve only the first row from one of the tables. Each method is explained with syntax, examples, and outputs to ensure clarity and practical understanding.

1. Using Subquery With LIMIT

The LIMIT clause restricts the number of rows we want to fetch from tables. This approach is widely used in MySQL to fetch the topmost row for a given condition.

Syntax

SELECT *
FROM table1 t1
JOIN (
    SELECT *
    FROM table2
    ORDER BY column_name
    LIMIT num_of_rows
) AS t2 ON t1.common_column = t2.common_column;

Key Terms

JOIN: Combines rows from two or more tables based on a related column between them.
AS: Renames a column or table with an alias to improve readability.
SELECT: Retrieves data from one or more tables or expressions.
ORDER BY: Sorts the result set based on specified columns or expressions.
LIMIT: Constrains the number of rows returned in the result set.

Example: Customer Details with Earliest Order Date

1. Customer table

CREATE table customers(customer_id int, customer_name varchar2, email varchar2);
-- Insert values into the customers table
INSERT INTO customers VALUES (1, 'John Doe', 'john.doe@example.com'); 
INSERT INTO customers VALUES (2, 'Jane Smith', 'jane.smith@example.com');

2. Orders table

CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT, order_date DATE ); 
 -- Insert values into the orders table
INSERT INTO orders VALUES (101, 1, '2024-01-15');

In this example, we are fetching the details of customer from the table customer and order. In this example, we use nested query and LIMIT which constraints the number of rows returned in the list.

Query:

SELECT *
FROM customers c
JOIN (
    SELECT *
    FROM orders
    ORDER BY order_date ASC
    LIMIT 1
) AS o ON c.customer_id = o.customer_id;

Output

subquery-with-limit — Subquery with Limit

Explanation:

The subquery retrieves the earliest order (using ORDER BY order_date ASC LIMIT 1).
The outer query joins the customers table with the result, providing details of the customer who placed the earliest order.

2. Using Subquery With TOP

The TOP keyword is commonly used in SQL Server to fetch a specific number of rows from a query. It works similarly to LIMIT in MySQL. Using this approach we will fetch only top most row from the corresponding table.

Syntax

SELECT *
FROM table1 t1
JOIN (
    SELECT TOP 1 *
    FROM table2
    ORDER BY column_name
) AS t2 ON t1.common_column = t2.common_column;

Key Terms

TOP 1: Selects only the first row from a result set based on specified criteria.
JOIN: Combines rows from different tables based on a related column.

Example: Fetching Earliest Order for Each Customer

In this example, we are fetching the details of customer from the table customer and order. In this example, we use nested query and TOP which Selects only the first row from a result set based on specified criteria.

Query:

SELECT *
FROM customer c
JOIN (
    SELECT TOP 1 *
    FROM order
    ORDER BY order_date ASC
) AS o ON c.customer_id = o.customer_id;

Output

Explanation:

The subquery uses TOP 1 to select the earliest order by order_date.
The outer query joins the result with the customers table to provide the relevant customer details.

3. Using Subquery With FETCH FIRST

The FETCH FIRST clause is supported in databases like Oracle and DB2. It retrieves a specified number of rows, similar to LIMIT or TOP. Using this approach we fetch first number of rows of corresponding tables.

Syntax

 SELECT *
FROM table1 t1
JOIN (
    SELECT *
    FROM table2
    ORDER BY column_name
    FETCH FIRST 1 ROW ONLY
) AS t2 ON t1.common_column = t2.common_column;

Key Terms

FETCH FIRST 1 ROW ONLY: Limits the number of rows retrieved to only the first row, according to the specified ordering.
ORDER BY: Specifies the column(s) by which the result set should be sorted, often in ascending or descending order.

Example: Retrieving Customer Details with Earliest Order Using FETCH FIRST

In this example, we are fetching the details of customer from the table customer and order. In this example, we use nested query and FETCH FIRST 1 ROW ONLY which Limits the number of rows retrieved to only the first row, according to the specified ordering.

Query:

SELECT *
FROM customer c
JOIN (
    SELECT *
    FROM order
    ORDER BY order_date ASC
     FETCH FIRST 1 ROW ONLY
) AS o ON c.customer_id = o.customer_id;

Output

Explanation:

The FETCH FIRST 1 ROW ONLY clause ensures that only the first row (based on order_date) is retrieved.
The result is joined with the customers table to fetch relevant details.

Conclusion

In summary, we explored three effective methods to join and retrieve the first row in SQL, tailored to different database systems. In MySQL, the LIMIT clause was used to fetch the top row from a related table. For SQL Server, the TOP keyword provided a similar solution. Lastly, in Oracle and other systems, the FETCH FIRST clause efficiently retrieved the first row. These flexible techniques streamline data retrieval, allowing precise and efficient queries, making them essential tools for database management.