0

I used these two queries for this database and the resutl was the same in both case so what the purpose of

"persons.firstname" or "orders.ordername"

what the difference ??

select** persons.firstname**,orders.ordername from persons inner join orders on persons.p_id=orders.p_id 

select **firstname,ordername** from persons inner join orders on persons.p_id=orders.p_id 



CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
FirstName varchar(255),
LastName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT 'unknown'
);

CREATE TABLE Orders
(
O_Id int NOT NULL,
P_Id int,
OrderName varchar(255),
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

insert into persons values ('1','sara','lala','street10','de');
insert into persons values ('2','toto','no','street10','nl');
insert into persons values ('3','momo','yes','street10','ch');
insert into persons values ('4','bobo','yea','street10','sy');
insert into persons values ('5','malk','tata','street10','sy');
insert into persons (P_Id,FirstName,LastName,Address) values ('6','yuo','zoo','street10');


insert into Orders values ('1','1','Order1');
insert into Orders values ('2','1','Order2');
insert into Orders values ('3','2','Order3');
insert into Orders values ('4','3','Order4');
insert into Orders values ('5','3','Order5');
insert into Orders values ('6','3','Order6');
insert into Orders (O_Id,OrderName) values ('7','Order7');
insert into Orders (O_Id,OrderName) values ('8','Order8');
insert into Orders (O_Id,OrderName) values ('9','Order9');

thanks alot :)

Edited by xXghostXx

2
Contributors
4
Replies
9
Views
4 Years
Discussion Span
Last Post by xXghostXx
1

Prefixing the column name with the table name is to differentiate the columns in case both tables contain columns with the same name.

For example, if I have two tables
Students (student_id, last_name, first_name)
Teachers (teacher_id, last_name, first_name)

and I want to write a query showing all the students that are also teachers (like a graduate assistant, for example) it would look like this:

select *
from students
inner join teachers
on students.last_name = teachers.last_name
and students.first_name = teachers.first_name

That would show the result set:
(student_id, last_name, first_name, teacher_id, last_name, first_name)

So if you only wanted to show the names from ONE of the tables, you would use the table name as a qualifier like so:

select student_id, students.last_name, students.first_name, teacher_id
from...
etc...

If you didn't qualify which name, executing your query would raise an error.

Sometimes programmers will assign an "alias" to tables so they don't have to continually spell out the table name. This is particularly useful if you have a table with a really long name. Using the arbitrary alias "x" for students and "y" for teachers in our above example would give:

select student_id, x.last_name, x.first_name, teacher_id
from students x
inner join teachers y
on x.last_name = y.last_name
and x.first_name = y.first_name

Notice that it is not necessary to qualify the columns that are uniquely named.

I hope this explanation helped you understand. Good luck, and happy coding!

Votes + Comments
Excellent explanation!
0

ummmmm thanksa lot alot man but i did not see the difference
same result what the Prefixing change the code

1

For your specific case, there is no difference. All the columns in the query have unique names, therefore you can either use the prefixes or not as you choose.

The only difference is readability. It gives you explicit reference to the table that a specific column comes from and has no effect on the execution of the query.

You asked what the purpose was, and I explained the purpose. Whether or not that purpose applies to your case is (I believe) immaterial.

0

uh thanks i just put an example to understand it and to apply it on general
thanks anyway coz im link it to c# where the . is like when ur calling something

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.