PostgreSQL INNER JoinIn this section, we are going to understand the working of PostgreSQL inner join, which is used to select data from many tables. We also learn how to use WHERE clause, USING clause, operators, and join three tables, table-aliasing in PostgreSQL inner join. What is PostgreSQL INNER JOIN clause? In a relational database, data is naturally spread in more than one table, and to select aggregate data, we often need to select data from various tables. The PostgreSQL Inner Join is used to return only those records from the tables, which are equivalent to the defined condition and hides other rows and columns. In PostgreSQL, it as a default Join, therefore it is not compulsory to use the Inner Join keyword with the query. The following Venn diagram displays the PostgreSQL inner join where we can easily understand that the Inner Join returns only the matching records from Table1 and Table2: PostgreSQL Inner Join SyntaxThe Inner Join keyword is used with the SELECT command and must be written after the FROM clause. The below syntaxes describe it more clearly: Syntax of PostgreSQL Inner Join with USING clause Syntax of PostgreSQL inner Join with WHERE Clause We will follow the below steps to join Table A with Table B:
Note: The Join condition returns the similar rows between the tables described in the Inner condition.Example of PostgreSQL Inner joinLet us see an example to understand how the PostgreSQL inner join works: To join two tables by using PostgreSQL INNER JOINFor this, we will create two tables named Employee and department table with the help of the CREATE command and inserting some values by using the INSERT command. Firstly, we are going to create an Employee and department tables by using the CREATE command: The below command is used to create a Department table: The Employee and department tables have been successfully created after executing the above commands. Once the both the tables have been generated, we are ready to insert some values into it by using the INSERT command as follows: In the below command, we are inserting the values in the department table: After creating and inserting the values in the Employee and department table, we will get the following output: Table1: Employee Table2: department The below query is used to select records from both tables (Employee and department): Output Once we implemented the above command, we will get the below result: Working of PostgreSQL inner join
Table-aliasing with PostgreSQL inner JoinGenerally, the tables we want to join will have columns with a similar name like the emp_id column. If we reference columns with a similar name from different tables in a command, the error will have occurred, and to avoid this particular error, we need to use the below syntax. In real-time, we will use table aliases to assign the joined tables short names to make the command more understandable. In the below command, we will use the table aliasing, and it returns the similar outcome as above: Output Once we implemented the above command, we will get the below output: PostgreSQL inner join with USING ClauseHere, we will see how the PostgreSQL inner join works with the USING clause because sometimes the name of the columns is similar in both the tables; that?s why we can use the USING clause to get the values. In the below example, we are using the USING clause as both tables have a similar emp_id column. Output After executing the above statement, we will get the below result: PostgreSQL inner join using WHERE clauseWe can also use the inner join with a WHERE condition. The WHERE clause allows us to return the filter outcome. In the below example, we will select rows from both tables Employee and department where dept_name is equal to Sales: Output After successful execution of the above command, it will give the below output: To join three tables using PostgreSQL INNER JOINIn the above section of this tutorial, we have already created two tables as Employee and department. Now, we want to join one more table and get the records from that particular table with the help of the Inner join. So, for this, we will create one more table as Jobs by using CREATE command as we can see in the following command: Once the Jobs table has been created successfully, we will insert some values into it with the help of INSERT command as we can see in the following command: After creating and inserting the values in Jobs table, we will get the following output: Table3: Jobs The below command is used to join the three tables, such as Employee, department, and Jobs. So, we will use the second INNER JOIN clause after the first INNER JOIN clause: Output Once we implemented the above query, we will get the below result: Note: To join more than three tables, we can use the above method.PostgreSQL Inner Join using OperatorsPostgreSQL allows many operators, which we can use with Inner Join, like equal (=), not equal (!=), greater than (>), less than (<), etc. In the below example, we are using the not equal (!=) operator with inner join: Output Once we click on the Execute button, we will get the following output that displays those values whose dept_name is not equal to SALES. OverviewIn the PostgreSQL inner join section, we have learned the following topics:
Next TopicPostgreSQL Left Join |