CODEWITHSUNDEEP
sqloracle-database
jackxu
jackxu

Reputation: 11

Oracle: query the tables, but no results

I have 2 tables:

CREATE TABLE teacher (
    tid varchar2(40),
    tname varchar2(20)
);

CREATE TABLE student (
    sid varchar2(40),
    sname varchar2(20),
    tid varchar2(40)
);

There is a lot of data in these tables, but when I execute the SQL:

SELECT *
FROM student s, teacher t
WHERE s.tid = t.tid

there is nothing. I'm confused about it, why?

Is there anything to do with the "tid varchar2(40)"?

Upvotes: 0

Views: 136

Answers (3)

David Aldridge
David Aldridge

Reputation: 52376

It's pretty unusual for a column like that to be defined as varchar2. Usually key columns would be integers, and you would not get into this situation.

You should probably look at rebuilding the schema using a more appropriate data type.

Upvotes: 0

mvp
mvp

Reputation: 116407

It is possible that your tid or sid fields have invisible trailing spaces. Try to sanitize these fields by trimming whitespace:

UPDATE teacher SET tid = TRIM(tid);
UPDATE student SET tid = TRIM(tid), sid = TRIM(sid);

Upvotes: 0

DB_learner
DB_learner

Reputation: 1026

Check if you have same tid in both tables. If you see both having same id and still no result, then you have trailing space or any specail characters at the end(Since ID is referred as Varchar here). Try trim in your query.

SELECT *
FROM student s, teacher t
WHERE trim(s.tid) = trim(t.tid);

Upvotes: 1

Related Questions