SQL Equi Join
What is Equi Join in SQL?
SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.
You may also perform EQUI JOIN by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality.
Pictorial presentation of SQL Equi Join:
Syntax:
SELECT column_list FROM table1, table2.... WHERE table1.column_name = table2.column_name;
or
SELECT * FROM table1 JOIN table2 [ON (join_condition)]
Example:
Here is an example of Equi Join in SQL.
Sample table: agents
Sample table: customer
To get agent name column from agents table and cust name and cust city columns from customer table after joining said two tables with the following condition -
1. working area of agents and customer city of customer table must be same,
the following SQL statement can be used:
SQL Code:
SELECT agents.agent_name,customer.cust_name,
customer.cust_city
FROM agents,customer
WHERE agents.working_area=customer.cust_city;
Output:
AGENT_NAME CUST_NAME CUST_CITY ---------------------------------------- ---------------------------------------- ------------ Ravi Kumar Ravindran Bangalore Ramasundar Ravindran Bangalore Subbarao Ravindran Bangalore Ravi Kumar Srinivas Bangalore Ramasundar Srinivas Bangalore Subbarao Srinivas Bangalore Ravi Kumar Rangarappa Bangalore Ramasundar Rangarappa Bangalore Subbarao Rangarappa Bangalore Ravi Kumar Venkatpati Bangalore Ramasundar Venkatpati Bangalore Subbarao Venkatpati Bangalore Anderson Fleming Brisban Anderson Jacks Brisban Anderson Winston Brisban Santakumar Yearannaidu Chennai ........... ...........
What is the difference between Equi Join and Inner Join in SQL?
An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns.
An inner join is a join of two or more tables that returns only those rows (compared using a comparison operator) that satisfy the join condition.
Pictorial presentation : SQL Equi Join Vs. SQL Inner Join
Key points to remember
Click on the following to get the slides presentation -
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
Previous: Introduction
Next: SQL NON EQUI JOIN
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework