Joins in Sql Server

Joins in SQL SERVER 2008 R2 :

Query :


SELECT
a.[d_no],
a.[l_no],
a.[m_cd],
b.[m_desc],
a.[p_cd]
FROM [Test].[db].[TABLE1] a left outer join [Test].[db].[table2] b
on a.[m_cd]= b.[m_cd]
where <Your conditions>

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

 

How to get top " n " number of rows from table

Suppose you want top n values from table , so execute following query
with replacement of following item as mention below :

1 )  Replace your our tablename with table_name]

2 )  Replace your "n" with row numbers  (how many rows you want )

[

Note :  You can do multiply and division operations also

]

Query for Top rows :

SELECT

MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 1 + 2 ) ORDER BY Row_Number;


Query for Bottom  rows :

SELECT MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,
COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 10 - 2 ) ORDER BY Row_Number;

WHAT IS - > " SELECT * FROM [TABLE_NAME] WHERE 1=1 " ??

What is

select * from xxx where 1=0 what does this mean?
also there is one
select * from yyy where 1=1

??
??

ANSWER ->


1. Select * from table where 1=0
return just header of the fields (attribute)
but with 0 rows

2. Select * from table where 1=1
return whole table entries
this is same as select * from table


Simple ans.

Logical ?

If the list of conditions is not known at compile time and is instead built at run time, you don't have to worry about whether you have one or more than one condition. You can generate them all like:

and <condition> 

and concatenate them all together. With the 1=1 at the start, the initial and has something to associate with.
I've never seen this used for any kind of injection protection, as you say it doesn't seem like it would help much. I have seen it used as an implementation convenience. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact.


 

" DUAL " IN SQL SERVER / ORACLE

What is DUAL in Oracle?

Dual is a table that is created by Oracle together with data dictionary. It consists of exactly one column named “dummy”, and one record. The value of that record is X.

You can check the content of the DUAL table using the following syntax.

SELECT * FROM dual

It will return only one record with the value ‘X’.

What is the reason for following error in SQL Server?

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dual’.

The reason behind the error shown above is your attempt to SELECT values from DUAL table in SQL Server. This table does not exist in SQL Server. Continue reading for workaround.

What is the Equivalent of DUAL in SQL Server to get current datetime?

Oracle:

select sysdate from dual

SQL Server:

SELECT GETDATE()

What is the equivalent of DUAL in SQL Server?

None. There is no need of Dual table in SQL Server at all.

Oracle:

select ‘something’ from dual

SQL Server:

SELECT ‘something’

I have to have DUAL table in SQL Server, what is the workaround?

If you have transferred your code from Oracle and you do not want to remove DUAL yet, you can create a DUAL table yourself in the SQL Server and use it.

Here is a quick script to do the said procedure.

CREATE TABLE DUAL(DUMMY VARCHAR(1)
)GOINSERT INTO DUAL (DUMMY)VALUES ('X')GO

After creating the DUAL table above just like in Oracle, you can now use DUAL table in SQL Server.