how to use joins

Question

I have to know about joins mean when and where should to use

Ananth G · Answer

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. Consider the following two tables (a) CUSTOMERS table is as follows: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ (b) Another table is ORDERS as follows: +-----+---------------------+-------------+--------+ |OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20 00:00:00 | 2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+ Now let us join these two tables in our SELECT statement as follows: SQL> SELECT ID NAME AGE AMOUNT FROM CUSTOMERS ORDERS WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID; This would produce the following result: +----+----------+-----+--------+ | ID | NAME | AGE | AMOUNT | +----+----------+-----+--------+ | 3 | kaushik | 23 | 3000 | | 3 | kaushik | 23 | 1500 | | 2 | Khilan | 25 | 1560 | | 4 | Chaitali | 25 | 2060 | +----+----------+-----+--------+ Here it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables such as =   = != BETWEEN LIKE and NOT; they can all be used to join tables. However the most common operator is the equal symbol. SQL Join Types: There are different types of joins available in SQL: INNER JOIN: returns rows when there is a match in both tables. LEFT JOIN: returns all rows from the left table even if there are no matches in the right table. RIGHT JOIN: returns all rows from the right table even if there are no matches in the left table. FULL JOIN: returns rows when there is a match in one of the tables. SELF JOIN: is used to join a table to itself as if the table were two tables temporarily renaming at least one table in the SQL statement. CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

Salman · Answer

Hi. Use joins when you want to retrieve the data from multiple tables. Suppose you have one table and you need some extra column which is not available in this table in that case you need to join some other table and retrieve the extra column value from other table.thanks.

Khaja Moizuddin · Answer

Joins are used for retrieving the data from more than one table. As SQL server or any other databases are relational databases which we can join tables by creating primary key in one table and foreign key in another table. Different types of joins are : 1)Inner join. 2)Outer join. a) Left outer join. b) Right outer join c) Full outer join. 3)Self join. 4)Cross join. 5)Equi-join. 6) Non Equi-join. While working with join a common column should be there in both the tables say table 1 is employee table with columns Employeeid EmployeenameEmployee Designation And table 2 say WorkExperience table.and it's columns say. Total experience Employeeid Joining Date. Here common column in both tables are Employeeid. 1)Inner join : It is used to retrieve matching records from both the tables. 2)Outer join. a) Left outer join : It is used to retrieve all the records from left table and matching records from right table. If there will be no matching records in right table then it displays with null value. b)Right outer join: It is used to retrieve all the records from right table and matching records from Left table. If there is no matching records it displays with null value. c)Full outer join : It is used to retrieve all the records from both the tables. If there is no matching records in both the tables then it displays with null value. 3)Cross join : It is Cartesian product of two or more tables. If there are m rows in Left table and n rows in right table then the Cartesian product of both the tables are m x n. 4)Self join : Self join means Joining the table by itself. Whenever working on self join we need to consider single table as two tables and then perform self join. 5)Equi join : It is used to retrieve the data from both the tables by using equal operator (=) 6)Non Equi-join: It is used to retrieve the data from both the tables without using equal operator. Hope this helps you.

Rajeesh Menoth · Answer

Hi SQL join is connecting multiple tables. For example one candidate is going to join one college.He/She want to register there own information in college db. So in this process we have one table it contain all student information and in some fields it's contain id of some other table. Example we pick particular branch id from branch table and insert into student information table. we never enter a branch name into the student information table because of tmrw Any one can change the name of the branch but can't able to change the id because it's a primary key. Reference : https://external.codecademy.com/courses/learn-sql/lessons/multiple-tables/exercises/inner-join

Nitin Sontakke · Answer

In case it is still not clear here is another way to look at it. Typically data is stored in database in a normalized fashion. To know what it means you will need to look into database normalization. One of the aspects of normalization is do not duplication the information. Meaning there should be only one table with the column CustomerName (may be CustomerMaster ). The CustomerMaster table should have one column say CustomerId  which should have values unique in all rows. This is called  Primary key . All other tables where customer information is required for example order table invoice table etc. should have CustomerId column only. This is called  Foreign key . Now you obviously want to have customer name printed on the invoice. But you don't have that column in the invoice table. You only have Customer Id. So to get customer name from Customer master table you will need to join Invoice table to that table on CustomerId column. Hope that clarifies a bit!