SQL-99 does not allow to delete data in a table when using JOINS. The reasons for this are quite obscure. Maybe it was considered too complicated to implement. Therefore for safety reasons it was not part of the standard.
As a result, MySQL implemented this feature and PostgreSQL rejected it.
The following query works under MySQL but fails under PostgreSQL:
FROM history AS h
INNER JOIN term_node AS tn ON (h.nid = tn.nid)
INNER JOIN term_data AS td ON (td.tid = tn.tid)
WHERE h.uid = 2067 AND td.vid = 2
create table 1 and table 2
CREATE TABLE Table1
FruitName Varchar( 100)
CREATE TABLE Table2
FruitName Varchar( 100)
Insert into Table1 Values ( 1,’Mango’ ), ( 2, ‘Graps’), (3, ‘Banana’);
Insert into Table2 Values ( 1,’Mango’ ), ( 2, ‘vitthal’), (4, ‘Orange’);
SELECT * FROM Table1 ;
SELECT * FROM Table2 ;
FROM Table1 T1 Inner Join Table2 T2
ON T1. Id = T2 .Id;
update table2 set FruitName=null where id>1
SET FruitName = t1.FruitName
FROM Table1 as t1 INNER JOIN Table2 T2
ON T1. Id = t2.Id;
Select distinct rows by using DISTINCT operator.
Filter rows by using WHERE clause.
Sort rows by using the ORDER BY clause.
Select rows based on various operators such as BETWEEN, IN and LIKE.
Group rows into groups by using GROUP BY clause
Apply condition for groups by using HAVING clause.
Join to another table by using INNER JOIN, LEFT JOIN, RIGHT JOIN clauses.
Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.
Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.
In other words it gives us combinations of each row of first table with all records in second table.
The inner JOIN is used to return rows from both tables that satisfy the given condition.
Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.
Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,
- Left Outer Join
- Right Outer Join
- Full Outer Join
Left Outer Join
The left outer join returns a result table with the matched data of two tables then remaining rows of the left table and null for the right table’s column.
Right Outer Join
The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table’s columns.
Full Outer Join
The full outer join returns a result table with the matched data of two table then remaining rows of both left table and then the right table.