{"id":246,"date":"2015-12-02T21:01:03","date_gmt":"2015-12-02T14:01:03","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=246"},"modified":"2022-04-03T15:01:39","modified_gmt":"2022-04-03T08:01:39","slug":"sqlite-self-join","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-self-join\/","title":{"rendered":"SQLite Self-Join"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn about a special type of join called SQLite self-join that allows you to join table to itself.<\/p>\r\n\r\n\r\n\r\n

Note that you should be familiar with  INNER JOIN<\/a><\/code> and LEFT JOIN<\/a><\/code> clauses before going forward with this tutorial.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQLite self-join<\/h2>\r\n\r\n\r\n\r\n

The self-join is a special kind of joins that allow you to join a table to itself using either LEFT JOIN<\/code> or INNER JOIN<\/code> clause. You use self-join to create a result set that joins the rows with the other rows within the same table.<\/p>\r\n\r\n\r\n\r\n

Because you cannot refer to the same table more than one in a query, you need to use a table alias to assign the table a different name when you use self-join.<\/p>\r\n\r\n\r\n\r\n

The self-join compares values of the same or different columns in the same table. Only one table is involved in the self-join.<\/p>\r\n\r\n\r\n\r\n

You often use self-join to query parents\/child relationship stored in a table or to obtain running totals.<\/p>\r\n\r\n\r\n\r\n

SQLite self-join examples<\/h2>\r\n\r\n\r\n\r\n

We will use the employees<\/code> table in the sample database<\/a> for demonstration.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

The employees<\/code> table stores not only employee data but also organizational data. The ReportsTo<\/code> column specifies the reporting relationship between employees.<\/p>\r\n\r\n\r\n\r\n

If an employee reports to a manager, the value of the ReportsTo<\/code> column of the employee’s row is equal to the value of the EmployeeId<\/code> column of the manager’s row. In case an employee does not report to anyone, the ReportsTo<\/code> column is NULL<\/code>.<\/p>\r\n\r\n\r\n\r\n

To get the information on who is the direct report of whom, you use the following statement:<\/p>\r\n\r\n\r\n

SELECT<\/span> m.firstname || ' '<\/span> || m.lastname AS<\/span> 'Manager'<\/span>,\r\n       e.firstname || ' '<\/span> || e.lastname AS<\/span> 'Direct report'<\/span> \r\nFROM<\/span> employees e\r\nINNER<\/span> JOIN<\/span> employees m ON<\/span> m.employeeid = e.reportsto\r\nORDER<\/span> BY<\/span> manager;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
The statement used the INNER JOIN<\/code> clause to join the employees<\/code> to itself. The employees<\/code> table has two roles: employees and managers.<\/p>\r\n\r\n\r\n\r\n
Because we used the INNER JOIN<\/code> clause to join the employees<\/code> table to itself, the result set does not have the row whose manager column contains a NULL<\/code> value.<\/p>\r\n\r\n\r\n\r\n
Note that the concatenation operator<\/a> ||<\/code> concatenates multiple strings into a single string. In the example, we use the concatenation operator to from the full names of the employees by concatenating the first name, space, and last name.<\/p>\r\n\r\n\r\n\r\n
In case you want to query the CEO who does not report to anyone, you need to change the INNER JOIN<\/code> clause to LEFT JOIN<\/code> clause in the query above.<\/p>\r\n\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
Andrew Adams<\/code> is the CEO because he does not report anyone.<\/p>\r\n\r\n\r\n\r\n
You can use the self-join technique to find the employees located in the same city as the following query:<\/p>\r\n\r\n\r\n
SELECT<\/span> DISTINCT<\/span>\r\n\te1.city,\r\n\te1.firstName || ' '<\/span> || e1.lastname AS<\/span> fullname\r\nFROM<\/span>\r\n\temployees e1\r\nINNER<\/span> JOIN<\/span> employees e2 ON<\/span> e2.city = e1.city \r\n   AND<\/span> (e1.firstname <> e2.firstname AND<\/span> e1.lastname <> e2.lastname)\r\nORDER<\/span> BY<\/span>\r\n\te1.city;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/a><\/figure>\r\n\r\n\r\n\r\n
The join condition has two expressions:<\/p>\r\n\r\n\r\n\r\n
  • e1.city = e2.city<\/code> to make sure that both employees located in the same city<\/li>
  • e.firstname <> e2.firstname AND e1.lastname <> e2.lastname<\/code> to ensure that e1<\/code> and e2<\/code> are not the same employee with the assumption that there aren’t employees who have the same first name and last name.<\/li><\/ul>\r\n\r\n\r\n\r\n
    In this tutorial, we have shown you how to use the SQLite self-join technique to join a table to itself.<\/p>\r\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n