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. Show
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:
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
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus. Previous: Introduction SQL: Tips of the DaySimple way to calculate median with MySQL: SELECT AVG(dd.val) as median_val FROM ( SELECT d.val, @rownum:[email protected]+1 as `row_number`, @total_rows:[email protected] FROM data d, (SELECT @rownum:=0) r WHERE d.val is NOT NULL -- put some where clause here ORDER BY d.val ) as dd WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) ); Database: MySQL Ref: https://bit.ly/3dso5PP What links tables on the basis of an equality condition that compares specified columns of each table?EquiJOIN links tables based on an equality condition that compares specified columns of each table.
What is used to establish relationships among tables and to ensure the integrity of the data?Keys are also used to establish relationships among tables and to ensure data integrity. The Primary Key is one or more attributes that uniquely identifies any given row (or tuple) AND the attribute(s) making up that Primary Key cannot be NULL .
What is the significance of the order of columns and rows in a relational table from a theoretical perspective?The tables of a relational database have some important characteristics: There is no significance to the order of the columns or rows. Each row contains one and only one value for each column. Each value for a given column has the same type.
When two or more tables share the same number of columns and when their corresponding?When two or more tables share the same number of columns, and when their corresponding columns share the same (or compatible) domains, they are said to be union-compatible.
|