Difference Between INNER JOIN and JOIN

Query using INNER JOIN

SELECT * FROM Table1 INNER JOIN  Table2 ON Table1.Column1 = Table2.Column1

Query using JOIN

SELECT * FROM Table1 JOIN  Table2 ON Table1.Column1 = Table2.Column1

The question is what is the difference between above two syntax.

Here is the answer – They are equal to each other. There is absolutely no difference between them. They are equal in performance as well as implementation. JOIN is actually shorter version of INNER JOIN.

Personally I prefer to write INNER JOIN because it is much cleaner to read and it avoids any confusion if there is related to JOIN. For example if users had written INNER JOIN instead of JOIN there would have been no confusion in mind and hence there was no need to have original question.

Source

Delete duplicate rows in SQL Server and Oracle

 SQL SERVER

DELETE FROM Table1 WHERE ID NOT IN
(  SELECT MAX(ID) FROM Table1
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3 )






WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM TableName
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO



AND In ORACLE 
 



DELETE FROM TABLE1
WHERE ROWID NOT IN (SELECT   MIN (ROWID)
FROM TABLE1
GROUP BY COLUMN1, COLUMN2, COLUMN3, COLUMN4);

Count of the number of records in a SQL Table

Name 3 ways to get an accurate count of the number of records in a SQL Table?

SELECT * FROM TableName

SELECT COUNT(*) FROM TableName

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('TableName') AND indid < 2